Last week, I wrote a post on using Axis Names on Cross Tables. My examples calculated the difference in Amounts between two scenarios on a cross table. I also needed to know the difference in well counts between scenarios, this time on a bar chart. But, when I changed the aggregation from a Sum to a CountDistinct, the calculations didn’t work anymore. That is because counts aggregate differently than the other aggregation methods. Read on if you want to get your counts right!
Blog or Video
Recap
Here’s a bit of a recap from last week. I wanted to calculate the difference in amount between scenarios on this cross table, and I used a custom expression to do that. Really, it was a little more complicated, but that is the short version. For the long version, check out the post.
This worked well, so when I needed to know the difference in well count from one scenario to another, I just changed out the aggregation from Sum to DistinctCount and Amount to Well Name. I expected it to work, but it didn’t. Instead, I got this. The delta in Scenario 4 is right, but Scenario 3 should be null.
And really I wanted this on a bar chart, and when I put this expression on a bar chart, this is what I get. So, Scenario 3 really needs to be null. Why is it not null?????
I spent the better part of an hour trying to figure this out. After all my troubleshooting yielded no answer, I decided to open a TIBCO support ticket. I wanted to show support how the expression worked with Amount but not with well count, so I took screenshots of my two cross tables. Then, I saw it. What do you see here that’s different between these two cross tables?
The difference is visible in the previous calculation. When using previous on Amount, the value is null because there is no previous. When using previous on well count, the value is zero. Thus 717-0 = 717. The calculation is technically right, but why is previous equal to 0.
Previous is equal to zero because it is counting. Apparently, in Spotfire, count aggregation methods always result in a number. When counting, null is not an option. When the value is null the count equals zero. Thus, counts aggregate differently than other functions.
Okay, great. I understand it. So, how do we fix it?
The Solution
We get creative with the expression using if/case logic and a strategically placed parenthesis.
For the bar chart:
(If(UniqueCount([well_completion_name]) over (Previous([Axis.Color]))=0,NULL,UniqueCount([well_completion_name]))) - UniqueCount([well_completion_name]) over (Previous([Axis.Color])) as [Well Count]For the cross table:
(If(UniqueCount([well_completion_name]) over (Previous([Axis.Columns]))=0,NULL,UniqueCount([well_completion_name]))) - UniqueCount([well_completion_name]) over (Previous([Axis.Columns])) as [Well Count]
And now you know correctly write expressions using Count and DistinctCount in Spotfire. If you found this useful, please share on LinkedIn or other social media. Thank you and have a great week!
Spotfire Version
Content created with Spotfire 10.3.