Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

jhillock
New Contributor II

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
Tags (1)
6 Replies
MVP
MVP

Re: Calculating Monthly Variance in Pivot

May be like this:

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

Where Replace CostExpression with your expression for Cost

MVP
MVP

Re: Calculating Monthly Variance in Pivot

Assuming is it Sum(Cost)

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

jhillock
New Contributor II

Re: Calculating Monthly Variance in Pivot

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

MVP
MVP

Re: Calculating Monthly Variance in Pivot

Can you elaborate with an example?

jhillock
New Contributor II

Re: Calculating Monthly Variance in Pivot

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.

MVP
MVP

Re: Calculating Monthly Variance in Pivot

May be like this

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

Community Browser