Variance of sales(Month - previous month sales)

[Kritika_Dhanbhar]

I'm trying to find the variance in sales ( Month - previous month sales) as in Jan-Dec, Feb-Jan, March-Feb, and so on...

This is what I've tried :

Sum({<
[Date.autoCalendar.Month]={"max([Date.autoCalendar.Month])-1"}
>}
[3rd Party Collection])

I'm getting certain values but they are incorrect.

I am making a bar chart that has the month as a dimension on the x-axis and the variance as a measure on the y-axis.

What am I doing wrong?

[hic · Accepted solution]

You cannot use set analysis for this. Set analysis is like a single selection that is made before the chart is evaluated, and you want different criteria on different rows in the chart.

But you can do it the following way: If you create a chart with

Dimension1: Date.autoCalendar.Month
Measure1: Sum([3rd Party Collection])
Measure2: Above(Sum([3rd Party Collection])) - Sum([3rd Party Collection])

then the second measure is probably what you want.

See also https://community.qlik.com/t5/Design/The-Above-Function/ba-p/1465357

[Kritika_Dhanbhar]

Thank you so much! It worked! Also, the guide was beneficial! 🙂

[Kritika_Dhanbhar]

Hello, this works for all the months except January....it doesn't display any value for it. I understand it is because the above function() does not get any value before Jan.Any Workaround for it?

[hic · Accepted solution]

Yes, and...?

I think it is correct to not show anything - the difference to previous month isn't defined. What would you like to show? If you know what you want to show, you can do it using e.g.

If(IsNull(Above(Sum([3rd Party Collection])), 'N/A', Above(Sum([3rd Party Collection])) - Sum([3rd Party Collection]))

or

If(RowNo()=1, 'N/A', Above(Sum([3rd Party Collection])) - Sum([3rd Party Collection]))

[Kritika_Dhanbhar]

Yes, it is correct. Thank you...This helped me get my desired output : )