I have an indicator on my dashboard, showing the daily change in cumulative subscription sales. When no sales were made on the prior day, the indicator just displays a dollar sign. I'd prefer it to display a zero.
I've tried revising the formula to:
IF(IsNull( (sum([Total Ticket Paid/Reserved Value]), [Days in Date])) , 0 , (sum([Total Ticket Paid/Reserved Value]), [Days in Date]))
Analytics accepts this formula, but this results in a yellow error icon being displayed when the formula dialog is closed.
What am I doing wrong? Is there another way to accomplish this? I'm pretty sure my parentheses are correct.
Thanks!
Hi Steven,
It's the double aggregation on SUM that you have in both instances of Ticket Paid/Reserved Value. Click on [Total Ticket Paid/Reserved Value] and select type (Sum) and change it to # All Items.
When in the Data Browser, and you select a value field with it's default aggregation of SUM, it shows up in the formula editor as [Total Whatever The Value]
If you type "SUM()" or select it from the Functions list to insert it into the formula editor, then the Value field we put into that SUM function should be of type All Items. Otherwise we end up with, in essence ( SUM ( SUM ( [Ticket Paid/Reserved Value] ) ) , [Days in Date] ). With that tweak... a zero.