topic Re: Rolling year on year average, how to calculate an average of an average? in QlikView https://community.qlik.com/t5/QlikView/Rolling-year-on-year-average-how-to-calculate-an-average-of-an/m-p/694824#M251213 <HTML><HEAD></HEAD><BODY><P>Thank you Bruno!</P><P></P><P>Nice name too! <IMG src="https://community.qlik.com/legacyfs/online/emoticons/silly.png" /></P></BODY></HTML> Wed, 18 Jun 2014 09:51:57 GMT 2014-06-18T09:51:57Z Rolling year on year average, how to calculate an average of an average? https://community.qlik.com/t5/QlikView/Rolling-year-on-year-average-how-to-calculate-an-average-of-an/m-p/694821#M251210 <HTML><HEAD></HEAD><BODY><P>Hi everyone,</P><P></P><P>So I have this following data:</P><P></P><TABLE border="0" cellpadding="0" cellspacing="0"><TBODY><TR><TD class="xl64" height="20" width="130">Origin</TD><TD class="xl64" width="130">Destination</TD><TD class="xl64" width="130">Passengers</TD><TD class="xl64" width="130">SegRevenueInUSD</TD><TD class="xl64" width="130">Period</TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">406</TD><TD class="xl64">61306</TD><TD class="xl63">01/01/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 151.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">556</TD><TD class="xl64">81732</TD><TD class="xl63">01/02/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 147.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">298</TD><TD class="xl64">38740</TD><TD class="xl63">01/03/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 130.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">394</TD><TD class="xl64">53190</TD><TD class="xl63">01/04/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 135.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">563</TD><TD class="xl64">84450</TD><TD class="xl63">01/05/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 150.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">630</TD><TD class="xl64">94500</TD><TD class="xl63">01/06/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 150.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">575</TD><TD class="xl64">74175</TD><TD class="xl63">01/07/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 129.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">372</TD><TD class="xl64">52080</TD><TD class="xl63">01/08/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 140.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">596</TD><TD class="xl64">76288</TD><TD class="xl63">01/09/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 128.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">552</TD><TD class="xl64">72864</TD><TD class="xl63">01/10/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 132.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">637</TD><TD class="xl64">77077</TD><TD class="xl63">01/11/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 121.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">480</TD><TD class="xl64">61440</TD><TD class="xl63">01/12/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 128.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">389</TD><TD class="xl64">48625</TD><TD class="xl63">01/01/2014</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 125.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">530</TD><TD class="xl64">75790</TD><TD class="xl63">01/02/2014</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 143.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">476</TD><TD class="xl64">68544</TD><TD class="xl63">01/03/2014</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 144.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">554</TD><TD class="xl64">83100</TD><TD class="xl63">01/04/2014</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 150.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">NYK</TD><TD class="xl64">552</TD><TD class="xl64">68448</TD><TD class="xl63">01/05/2014</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 124.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">600</TD><TD class="xl64">464400</TD><TD class="xl63">01/01/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 774.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">405</TD><TD class="xl64">168075</TD><TD class="xl63">01/02/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 415.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">421</TD><TD class="xl64">191134</TD><TD class="xl63">01/03/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 454.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">561</TD><TD class="xl64">335478</TD><TD class="xl63">01/04/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 598.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">564</TD><TD class="xl64">366600</TD><TD class="xl63">01/05/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 650.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">496</TD><TD class="xl64">333808</TD><TD class="xl63">01/06/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 673.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">424</TD><TD class="xl64">321392</TD><TD class="xl63">01/07/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 758.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">581</TD><TD class="xl64">270746</TD><TD class="xl63">01/08/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 466.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">592</TD><TD class="xl64">317904</TD><TD class="xl63">01/09/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 537.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">549</TD><TD class="xl64">328851</TD><TD class="xl63">01/10/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 599.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">461</TD><TD class="xl64">243408</TD><TD class="xl63">01/11/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 528.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">540</TD><TD class="xl64">398520</TD><TD class="xl63">01/12/2013</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 738.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">595</TD><TD class="xl64">268345</TD><TD class="xl63">01/01/2014</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 451.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">442</TD><TD class="xl64">228956</TD><TD class="xl63">01/02/2014</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 518.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">587</TD><TD class="xl64">274129</TD><TD class="xl63">01/03/2014</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 467.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">492</TD><TD class="xl64">392124</TD><TD class="xl63">01/04/2014</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 797.00 </TD></TR><TR><TD class="xl64" height="20">LDN</TD><TD class="xl64">ASF</TD><TD class="xl64">446</TD><TD class="xl64">351894</TD><TD class="xl63">01/05/2014</TD><TD class="xl65">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR />&nbsp; 789.00 </TD></TR></TBODY></TABLE><P></P><P>The empty Column being the average Revenue per Passenger. So [SegRevenueInUSD]/[Passengers]. </P><P></P><P>I want to calculate the rolling average per month for the previous 12 months. So that would sum up the totals for those columns for each of the months and then show them. So for Jan 2014, the total passengers are 984, and Revenue is 316970 and the average for that would be 322 as seen below:</P><P></P><TABLE border="0" cellpadding="0" cellspacing="0"><TBODY><TR><TD height="20" style="background: #dce6f1; border-width: medium medium 0.5pt; border-style: none none solid; border-color: currentColor currentColor #95b3d7; color: black; font-family: Calibri; font-size: 11pt; font-weight: bold;" width="91">Row Labels</TD><TD style="background: #dce6f1; border-width: medium medium 0.5pt; border-style: none none solid; border-color: currentColor currentColor #95b3d7; color: black; font-family: Calibri; font-size: 11pt; font-weight: bold;" width="91">Sum of<BR />&nbsp; SegRevenueInUSD</TD><TD style="background: #dce6f1; border-width: medium medium 0.5pt; border-style: none none solid; border-color: currentColor currentColor #95b3d7; color: black; font-family: Calibri; font-size: 11pt; font-weight: bold;" width="91">Sum of<BR />&nbsp; Passengers</TD><TD style="background: #dce6f1; border-width: medium medium 0.5pt; border-style: none none solid; border-color: currentColor currentColor #95b3d7; color: black; font-family: Calibri; font-size: 11pt; font-weight: bold;" width="91">Sum of<BR />&nbsp; AverageYieldInUSD</TD></TR><TR><TD class="xl66" height="20">01/01/2013</TD><TD align="right">525706</TD><TD align="right">1006</TD><TD align="right" class="xl67">523</TD></TR><TR><TD class="xl66" height="20">01/02/2013</TD><TD align="right">249807</TD><TD align="right">961</TD><TD align="right" class="xl67">260</TD></TR><TR><TD class="xl66" height="20">01/03/2013</TD><TD align="right">229874</TD><TD align="right">719</TD><TD align="right" class="xl67">320</TD></TR><TR><TD class="xl66" height="20">01/04/2013</TD><TD align="right">388668</TD><TD align="right">955</TD><TD align="right" class="xl67">407</TD></TR><TR><TD class="xl66" height="20">01/05/2013</TD><TD align="right">451050</TD><TD align="right">1127</TD><TD align="right" class="xl67">400</TD></TR><TR><TD class="xl66" height="20">01/06/2013</TD><TD align="right">428308</TD><TD align="right">1126</TD><TD align="right" class="xl67">380</TD></TR><TR><TD class="xl66" height="20">01/07/2013</TD><TD align="right">395567</TD><TD align="right">999</TD><TD align="right" class="xl67">396</TD></TR><TR><TD class="xl66" height="20">01/08/2013</TD><TD align="right">322826</TD><TD align="right">953</TD><TD align="right" class="xl67">339</TD></TR><TR><TD class="xl66" height="20">01/09/2013</TD><TD align="right">394192</TD><TD align="right">1188</TD><TD align="right" class="xl67">332</TD></TR><TR><TD class="xl66" height="20">01/10/2013</TD><TD align="right">401715</TD><TD align="right">1101</TD><TD align="right" class="xl67">365</TD></TR><TR><TD class="xl66" height="20">01/11/2013</TD><TD align="right">320485</TD><TD align="right">1098</TD><TD align="right" class="xl67">292</TD></TR><TR><TD class="xl66" height="20">01/12/2013</TD><TD align="right">459960</TD><TD align="right">1020</TD><TD align="right" class="xl67">451</TD></TR><TR><TD class="xl66" height="20">01/01/2014</TD><TD align="right">316970</TD><TD align="right">984</TD><TD align="right" class="xl67">322</TD></TR><TR><TD class="xl66" height="20">01/02/2014</TD><TD align="right">304746</TD><TD align="right">972</TD><TD align="right" class="xl67">314</TD></TR><TR><TD class="xl66" height="20">01/03/2014</TD><TD align="right">342673</TD><TD align="right">1063</TD><TD align="right" class="xl67">322</TD></TR><TR><TD class="xl66" height="20">01/04/2014</TD><TD align="right">475224</TD><TD align="right">1046</TD><TD align="right" class="xl67">454</TD></TR><TR><TD class="xl66" height="20">01/05/2014</TD><TD align="right">420342</TD><TD align="right">998</TD><TD align="right" class="xl67">421</TD></TR><TR><TD class="xl66" height="20" style="background: #dce6f1; border-width: 0.5pt medium medium; border-style: solid none none; border-color: #95b3d7 currentColor currentColor; color: black; font-family: Calibri; font-size: 11pt; font-weight: bold;">Grand<BR />&nbsp; Total</TD><TD align="right" style="background: #dce6f1; border-width: 0.5pt medium medium; border-style: solid none none; border-color: #95b3d7 currentColor currentColor; color: black; font-family: Calibri; font-size: 11pt; font-weight: bold;">6428113</TD><TD align="right" style="background: #dce6f1; border-width: 0.5pt medium medium; border-style: solid none none; border-color: #95b3d7 currentColor currentColor; color: black; font-family: Calibri; font-size: 11pt; font-weight: bold;">17316</TD><TD align="right" class="xl67" style="background: #dce6f1; border-width: 0.5pt medium medium; border-style: solid none none; border-color: #95b3d7 currentColor currentColor; color: black; font-family: Calibri; font-size: 11pt; font-weight: bold;">371</TD></TR></TBODY></TABLE><P></P><P>This however doesn't give me the rolling average, it just simply gives me an average of each one month. </P><P></P><P>This is the results that I want:</P><P></P><TABLE border="0" cellpadding="0" cellspacing="0"><TBODY><TR><TD class="xl66" height="20" width="130">01/01/2013</TD><TD class="xl65" width="130">525706</TD><TD class="xl65" width="130">1006</TD><TD class="xl67" width="130">523</TD></TR><TR><TD class="xl66" height="20">01/02/2013</TD><TD class="xl65">775513</TD><TD class="xl65">1967</TD><TD class="xl67">394</TD></TR><TR><TD class="xl66" height="20">01/03/2013</TD><TD class="xl65">1005387</TD><TD class="xl65">2686</TD><TD class="xl67">374</TD></TR><TR><TD class="xl66" height="20">01/04/2013</TD><TD class="xl65">1394055</TD><TD class="xl65">3641</TD><TD class="xl67">383</TD></TR><TR><TD class="xl66" height="20">01/05/2013</TD><TD class="xl65">1845105</TD><TD class="xl65">4768</TD><TD class="xl67">387</TD></TR><TR><TD class="xl66" height="20">01/06/2013</TD><TD class="xl65">2273413</TD><TD class="xl65">5894</TD><TD class="xl67">386</TD></TR><TR><TD class="xl66" height="20">01/07/2013</TD><TD class="xl65">2668980</TD><TD class="xl65">6893</TD><TD class="xl67">387</TD></TR><TR><TD class="xl66" height="20">01/08/2013</TD><TD class="xl65">2991806</TD><TD class="xl65">7846</TD><TD class="xl67">381</TD></TR><TR><TD class="xl66" height="20">01/09/2013</TD><TD class="xl65">3385998</TD><TD class="xl65">9034</TD><TD class="xl67">375</TD></TR><TR><TD class="xl66" height="20">01/10/2013</TD><TD class="xl65">3787713</TD><TD class="xl65">10135</TD><TD class="xl67">374</TD></TR><TR><TD class="xl66" height="20">01/11/2013</TD><TD class="xl65">4108198</TD><TD class="xl65">11233</TD><TD class="xl67">366</TD></TR><TR><TD class="xl66" height="20">01/12/2013</TD><TD class="xl65">4568158</TD><TD class="xl65">12253</TD><TD class="xl67">373</TD></TR><TR><TD class="xl66" height="20">01/01/2014</TD><TD class="xl65">4359422</TD><TD class="xl65">12231</TD><TD class="xl67">356</TD></TR><TR><TD class="xl66" height="20">01/02/2014</TD><TD class="xl65">4414361</TD><TD class="xl65">12242</TD><TD class="xl67">361</TD></TR><TR><TD class="xl66" height="20">01/03/2014</TD><TD class="xl65">4527160</TD><TD class="xl65">12586</TD><TD class="xl67">360</TD></TR><TR><TD class="xl66" height="20">01/04/2014</TD><TD class="xl65">4613716</TD><TD class="xl65">12677</TD><TD class="xl67">364</TD></TR><TR><TD class="xl66" height="20">01/05/2014</TD><TD class="xl65">4583008</TD><TD class="xl65">12548</TD><TD class="xl67">365</TD></TR></TBODY></TABLE><P></P><P>So the 2nd column there would be an aculumation of the last 12 months, same for the 3rd column. and then the average is calculated to give me the correct result.</P><P></P><P>The formula i am currently using to try and do this is:</P><P></P><P></P><P></P><P><SPAN style="font-size: 8pt;"><SPAN style="color: #0000ff; font-size: 8pt;">rangeavg</SPAN>(</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">above</SPAN><SPAN style="font-size: 8pt;">(<BR /><BR /><BR /><BR />(</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">Sum</SPAN><SPAN style="font-size: 8pt;">(SegRevenueInUSD</SPAN><SPAN style="font-size: 8pt;">)/</SPAN><SPAN style="color: #0000ff; font-size: 8pt;">Sum</SPAN><SPAN style="font-size: 8pt;">(Passengers</SPAN><SPAN style="font-size: 8pt;">)<BR /><BR /><BR /><BR />),0,12)) </SPAN></P><P></P><P><SPAN style="font-size: 8pt;">Does anyone know how to solve this?</SPAN></P><P></P><P><SPAN style="font-size: 8pt;">Let me know if you need anything else..</SPAN></P><P></P><P><SPAN style="font-size: 8pt;">Many thanks,</SPAN></P><P></P><P><SPAN style="font-size: 8pt;">Bruno Pereira</SPAN></P></BODY></HTML> Tue, 17 Jun 2014 14:45:31 GMT https://community.qlik.com/t5/QlikView/Rolling-year-on-year-average-how-to-calculate-an-average-of-an/m-p/694821#M251210 2014-06-17T14:45:31Z Re: Rolling year on year average, how to calculate an average of an average? https://community.qlik.com/t5/QlikView/Rolling-year-on-year-average-how-to-calculate-an-average-of-an/m-p/694822#M251211 <HTML><HEAD></HEAD><BODY><P>Hi.</P><P></P><P>You can use AsOfTable approach like described here:</P><P><A href="https://community.qlik.com/docs/DOC-4252">Calculating rolling n-period totals, averages or other aggregations</A></P></BODY></HTML> Tue, 17 Jun 2014 16:36:33 GMT https://community.qlik.com/t5/QlikView/Rolling-year-on-year-average-how-to-calculate-an-average-of-an/m-p/694822#M251211 whiteline 2014-06-17T16:36:33Z Re: Rolling year on year average, how to calculate an average of an average? https://community.qlik.com/t5/QlikView/Rolling-year-on-year-average-how-to-calculate-an-average-of-an/m-p/694823#M251212 <HTML><HEAD></HEAD><BODY><P>Hello!</P><P></P><P>You shouldn't average the average. You need to sum the SerRev, sum the Passengers and then divide one by the other. Like this:</P><P></P><P><STRONG>RangeSum(Above(Sum(SegRevenueInUSD), 0, 12))/RangeSum(Above(Sum(Passengers), 0, 12))</STRONG></P><P></P><P>If you need a total row, use this expression:</P><P></P><P><STRONG>(Sum(SegRevenueInUSD) + RangeSum(Above(Sum(SegRevenueInUSD), 1, 11)))/(Sum(Passengers) + RangeSum(Above(Sum(Passengers), 1, 11)))</STRONG></P><P></P><P>Hope it helps.</P></BODY></HTML> Tue, 17 Jun 2014 17:18:24 GMT https://community.qlik.com/t5/QlikView/Rolling-year-on-year-average-how-to-calculate-an-average-of-an/m-p/694823#M251212 Anonymous 2014-06-17T17:18:24Z Re: Rolling year on year average, how to calculate an average of an average? https://community.qlik.com/t5/QlikView/Rolling-year-on-year-average-how-to-calculate-an-average-of-an/m-p/694824#M251213 <HTML><HEAD></HEAD><BODY><P>Thank you Bruno!</P><P></P><P>Nice name too! <IMG src="https://community.qlik.com/legacyfs/online/emoticons/silly.png" /></P></BODY></HTML> Wed, 18 Jun 2014 09:51:57 GMT https://community.qlik.com/t5/QlikView/Rolling-year-on-year-average-how-to-calculate-an-average-of-an/m-p/694824#M251213 2014-06-18T09:51:57Z