Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculating Monthly Variance in Pivot

I have a pivot table in which I'm trying to calculate a yearly average in a column, but having the month as a dimension is throwing it off. An idea for the final output would be like the table below. Any thoughts?

MonthCostYear AverageVariance

Jan

10

21-11
Feb1521-6
Mar2021-1
Apr1521-6
May521-16
Jun452124
Jul30219
Aug2021-1
Sep1521-6
Oct30219
Nov2021-1
Dec25214
6 Replies
sunny_talwar

May be like this:

CostExpression - Avg(TOTAL Aggr(CostExpression, Month))

Where Replace CostExpression with your expression for Cost

sunny_talwar

Assuming is it Sum(Cost)

Sum(Cost) - Avg(TOTAL Aggr(Sum(Cost), Month))

Anonymous
Not applicable
Author

Thank you! That worked. What if I wanted to run it as a rolling n-period average?

sunny_talwar

Can you elaborate with an example?

Anonymous
Not applicable
Author

Yes, so in the same table above, if I wanted the December number to the a three-period average, it would calculate the average of December, November, and October. If I wanted the same calculation for August, it would calculate the average for August, July, and June.

sunny_talwar

May be like this

CostExpression - RangeAvg(Above(CostExpression, 0, 3))