Qlik Community

Ask a Question

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

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

May be like this:

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

Where Replace CostExpression with your expression for Cost

Assuming is it Sum(Cost)

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

Contributor III
Contributor III

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

Can you elaborate with an example?

Contributor III
Contributor III

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.

May be like this

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