Month-over-Month variance table

poklegoguy — Mon, 25 Sep 2023 05:53:15 GMT

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?

Re: Month-over-Month variance table

sbaro_bd — Wed, 27 Sep 2023 14:22:22 GMT

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.

topic Month-over-Month variance table in QlikView

Month-over-Month variance table

Re: Month-over-Month variance table