People always ask us if there is a way to show changes in values from within the same column over different intervals, like weeks or months. For example, what was the sales this week compared to the sales last week, how much did a patient weight this visit compared to last visit, or what was the change in stock price from this quarter compared to last quarter? You can do this using a custom expression, but then the values are not available to use elsewhere. If you wish to view them elsewhere (like in a table plot), you will need to use a calculated column.
Let’s assume we have the following data loaded that displays sales data per week:
.
If we plot this on a Line Chart , as shown below, we can see the trend of sales per week.
We may want to be more specific and show the change in sales from one week to another. For this, we can use the previous Node Navigation Method in a calculated column expression as shown below:
The result will be a new column called Weekly Change. We can then plot the Weekly Change on a Bar chart as shown below.
What if your data contains another category you want to group by? Say you have multiple salespeople and you want to look at weekly change per salesperson?
For this you can use the same expression as before , but also use the Intersect method to say to group by the Salesperson column.
Once this is done, the newly created column contains the weekly change per salesperson.
Notice the Weekly Change value for the first row is empty, since there was no previous value to compare it to. You can add methods into the expression to remove the null value with a zero if desired.
You can also plot this on a Bar Chart using the Trellis feature to Trellis by Salesperson to see their individual weekly change.
Keep in mind the differences between using calculated columns and custom expressions. If you filter your data, the custom expression will be recalculated, but the calculated column will not be.
If you want more information on the Intersect method, the previous method (or any other Node Navigation) methods, please take our SP 141 Computational Analytics course using one of our three delivery options: onsite, regional, or blended. This course will teach you all the core concepts required to understand the Spotfire Expression Language in either a calculated column or a custom expression.