Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I am trying to create a table chart that has months with 'MMM' format as dimension and Total Sales, Sales Last Month and Variance as expressions. The desired result will look something like this:
Month | Total Sales | Sales Last Month | Variance |
Jan | 100 | - | 0 |
Feb | 150 | 100 | 50 |
Mar | 120 | 150 | -30 |
Apr | 100 | 120 | -20 |
I have tried using Above() function for this case, it worked but is very limited in flexibility. The table will not worked if users made a selection like month. I have tried with expressions like:
SUM({$<Month = {"$(=Max(Month-1)"}>} sales)
for the 'Sales Last Month' expression but instead of displaying previous month's sales, it just returns me the same values as in the 'Total Sales' column. I have looked at a lot of posts in this community but still can't find a solution for it. Can I know how can I create a table as above without using Above() function so it can still display the correct values if users made selections on month? Or is that possible?
Hi @poklegoguy ,
If you don't want to use Above(), you can try to create a new Sales field with Previous() function in your script (be aware to apply the good sorting for your data).
For the filter behavior, you can disable the effect of month filter on your calculations like this :
SUM({<Month = >} sales) OR SUM({<Month>} sales) (the equal sign is not mandatory).
Regards.