• Have you ever wondered what the IsValid column in the Edit > Column Properties dialog means or does?
  • Have you ever wanted a faster way to figure out if your column calculations are working than opening up each calculation individually?

This week, I am working on getting all the Ruths.ai Essentials courses converted to online content (YAY!), so that we have that as an option for clients.  I was recording a short video on the Column Properties dialog that explains what helpful information  you can see in this dialog.  I discussed the Column Type, Data Type, ExternalName, Expression and Origin columns because those are all very important.  I skipped over the IsValid column because I don’t really use it.  I’ve never really had to learn what it was for or what it did.  Today, however, I was curious.

For calculated columns in particular, the IsValid column tells you if the calculation is functioning (i.e. does it work).  Why would it not work you ask?  Well, perhaps a column that was used in the calculation is now missing, so the calculation is invalid.  This is one of the ways in which Spotfire differs from Excel.  In Excel, if you delete a column used in a calculation, all the calculated cells will read #NA, and it’s very obvious.  The same issue in Spotfire is not as obvious.  The result will be null, which you may or may not notice.

Here is a quick example that explains IsNull.

In this screen you can see I have a calculation called Avg Sale Price and a binned column that both use the Sale Price column.  All IsValid cells are shown to be true.

Then, I delete the Sale Price column.

Now, you can see that the IsValid column is False for both Avg Sale Price and Binned SalePrice.

Thus, the IsValid column is a good quick check that all calculations are working.  After replacing a table or matching columns, it’s a good practice to review the IsValid column and make sure they are all true.  This also tells you that if the value is ever False, it needs to be investigated.


Written by Julie Schellberg of Big Mountain Analytics, LLC
Residing in Whitefish, MT, an analytics partner with Ruths.ai. Specializing in Spotfire analytics, dabbling in Power BI and R.