topic Re: Calculating Monthly Variance in Pivot in QlikView https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240748#M518302 <HTML><HEAD></HEAD><BODY><P>Thank you! That worked. What if I wanted to run it as a rolling n-period average?</P></BODY></HTML> Thu, 26 Jan 2017 14:32:45 GMT Anonymous 2017-01-26T14:32:45Z Calculating Monthly Variance in Pivot https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240745#M518294 <HTML><HEAD></HEAD><BODY><P>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?</P><P></P><TABLE border="1" class="jiveBorder" style="border: 1px solid rgb(0, 0, 0); width: 100%;"><TBODY><TR><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Month</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Cost</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Year Average</STRONG></TH><TH style="text-align: center; background-color: #6690bc; color: #ffffff; padding: 2px;" valign="middle"><STRONG>Variance</STRONG></TH></TR><TR><TD style="padding: 2px;"><P>Jan</P></TD><TD style="padding: 2px;"><P>10</P></TD><TD style="padding: 2px;">21</TD><TD style="padding: 2px;">-11</TD></TR><TR><TD style="padding: 2px;">Feb</TD><TD style="padding: 2px;">15</TD><TD style="padding: 2px;"><SPAN style="font-size: 13.3333px;">21</SPAN></TD><TD style="padding: 2px;">-6</TD></TR><TR><TD style="padding: 2px;">Mar</TD><TD style="padding: 2px;">20</TD><TD style="padding: 2px;"><SPAN style="font-size: 13.3333px;">21</SPAN></TD><TD style="padding: 2px;">-1</TD></TR><TR><TD style="padding: 2px;">Apr</TD><TD style="padding: 2px;">15</TD><TD style="padding: 2px;"><SPAN style="font-size: 13.3333px;">21</SPAN></TD><TD style="padding: 2px;">-6</TD></TR><TR><TD style="padding: 2px;">May</TD><TD style="padding: 2px;">5</TD><TD style="padding: 2px;"><SPAN style="font-size: 13.3333px;">21</SPAN></TD><TD style="padding: 2px;">-16</TD></TR><TR><TD style="padding: 2px;">Jun</TD><TD style="padding: 2px;">45</TD><TD style="padding: 2px;"><SPAN style="font-size: 13.3333px;">21</SPAN></TD><TD style="padding: 2px;">24</TD></TR><TR><TD style="padding: 2px;">Jul</TD><TD style="padding: 2px;">30</TD><TD style="padding: 2px;"><SPAN style="font-size: 13.3333px;">21</SPAN></TD><TD style="padding: 2px;">9</TD></TR><TR><TD style="padding: 2px;">Aug</TD><TD style="padding: 2px;">20</TD><TD style="padding: 2px;"><SPAN style="font-size: 13.3333px;">21</SPAN></TD><TD style="padding: 2px;">-1</TD></TR><TR><TD style="padding: 2px;">Sep</TD><TD style="padding: 2px;">15</TD><TD style="padding: 2px;"><SPAN style="font-size: 13.3333px;">21</SPAN></TD><TD style="padding: 2px;">-6</TD></TR><TR><TD style="padding: 2px;">Oct</TD><TD style="padding: 2px;">30</TD><TD style="padding: 2px;"><SPAN style="font-size: 13.3333px;">21</SPAN></TD><TD style="padding: 2px;">9</TD></TR><TR><TD style="padding: 2px;">Nov</TD><TD style="padding: 2px;">20</TD><TD style="padding: 2px;"><SPAN style="font-size: 13.3333px;">21</SPAN></TD><TD style="padding: 2px;">-1</TD></TR><TR><TD style="padding: 2px;">Dec</TD><TD style="padding: 2px;">25</TD><TD style="padding: 2px;"><SPAN style="font-size: 13.3333px;">21</SPAN></TD><TD style="padding: 2px;">4</TD></TR></TBODY></TABLE></BODY></HTML> Thu, 26 Jan 2017 14:25:17 GMT https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240745#M518294 Anonymous 2017-01-26T14:25:17Z Re: Calculating Monthly Variance in Pivot https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240746#M518298 <HTML><HEAD></HEAD><BODY><P>May be like this:</P><P></P><P><STRONG>CostExpression - Avg(TOTAL Aggr(CostExpression, Month))</STRONG></P><P></P><P>Where Replace <STRONG>CostExpression</STRONG> with your expression for Cost</P></BODY></HTML> Thu, 26 Jan 2017 14:28:39 GMT https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240746#M518298 sunny_talwar 2017-01-26T14:28:39Z Re: Calculating Monthly Variance in Pivot https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240747#M518300 <HTML><HEAD></HEAD><BODY><P>Assuming is it Sum(Cost)</P><P></P><P><STRONG>Sum(Cost) - Avg(TOTAL Aggr(Sum(Cost), Month))</STRONG></P></BODY></HTML> Thu, 26 Jan 2017 14:29:35 GMT https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240747#M518300 sunny_talwar 2017-01-26T14:29:35Z Re: Calculating Monthly Variance in Pivot https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240748#M518302 <HTML><HEAD></HEAD><BODY><P>Thank you! That worked. What if I wanted to run it as a rolling n-period average?</P></BODY></HTML> Thu, 26 Jan 2017 14:32:45 GMT https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240748#M518302 Anonymous 2017-01-26T14:32:45Z Re: Calculating Monthly Variance in Pivot https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240749#M518303 <HTML><HEAD></HEAD><BODY><P>Can you elaborate with an example?</P></BODY></HTML> Thu, 26 Jan 2017 14:33:47 GMT https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240749#M518303 sunny_talwar 2017-01-26T14:33:47Z Re: Calculating Monthly Variance in Pivot https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240750#M518304 <HTML><HEAD></HEAD><BODY><P>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.</P></BODY></HTML> Thu, 26 Jan 2017 14:35:43 GMT https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240750#M518304 Anonymous 2017-01-26T14:35:43Z Re: Calculating Monthly Variance in Pivot https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240751#M518305 <HTML><HEAD></HEAD><BODY><P>May be like this</P><P></P><P><STRONG style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">CostExpression - RangeAvg(Above(CostExpression, 0, 3))</STRONG></P></BODY></HTML> Thu, 26 Jan 2017 14:37:40 GMT https://community.qlik.com/t5/QlikView/Calculating-Monthly-Variance-in-Pivot/m-p/1240751#M518305 sunny_talwar 2017-01-26T14:37:40Z