Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2014-06-17 10:45 AM

Rolling year on year average, how to calculate an average of an average?

Hi everyone,

So I have this following data:

OriginDestinationPassengersSegRevenueInUSDPeriod
LDNNYK4066130601/01/2013                         
  151.00
LDNNYK5568173201/02/2013                         
  147.00
LDNNYK2983874001/03/2013                         
  130.00
LDNNYK3945319001/04/2013                         
  135.00
LDNNYK5638445001/05/2013                         
  150.00
LDNNYK6309450001/06/2013                         
  150.00
LDNNYK5757417501/07/2013                         
  129.00
LDNNYK3725208001/08/2013                         
  140.00
LDNNYK5967628801/09/2013                         
  128.00
LDNNYK5527286401/10/2013                         
  132.00
LDNNYK6377707701/11/2013                         
  121.00
LDNNYK4806144001/12/2013                         
  128.00
LDNNYK3894862501/01/2014                         
  125.00
LDNNYK5307579001/02/2014                         
  143.00
LDNNYK4766854401/03/2014                         
  144.00
LDNNYK5548310001/04/2014                         
  150.00
LDNNYK5526844801/05/2014                         
  124.00
LDNASF60046440001/01/2013                         
  774.00
LDNASF40516807501/02/2013                         
  415.00
LDNASF42119113401/03/2013                         
  454.00
LDNASF56133547801/04/2013                         
  598.00
LDNASF56436660001/05/2013                         
  650.00
LDNASF49633380801/06/2013                         
  673.00
LDNASF42432139201/07/2013                         
  758.00
LDNASF58127074601/08/2013                         
  466.00
LDNASF59231790401/09/2013                         
  537.00
LDNASF54932885101/10/2013                         
  599.00
LDNASF46124340801/11/2013                         
  528.00
LDNASF54039852001/12/2013                         
  738.00
LDNASF59526834501/01/2014                         
  451.00
LDNASF44222895601/02/2014                         
  518.00
LDNASF58727412901/03/2014                         
  467.00
LDNASF49239212401/04/2014                         
  797.00
LDNASF44635189401/05/2014                         
  789.00

The empty Column being the average Revenue per Passenger. So [SegRevenueInUSD]/[Passengers].

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:

Row LabelsSum of
  SegRevenueInUSD		Sum of
  Passengers		Sum of
  AverageYieldInUSD
01/01/20135257061006523
01/02/2013249807961260
01/03/2013229874719320
01/04/2013388668955407
01/05/20134510501127400
01/06/20134283081126380
01/07/2013395567999396
01/08/2013322826953339
01/09/20133941921188332
01/10/20134017151101365
01/11/20133204851098292
01/12/20134599601020451
01/01/2014316970984322
01/02/2014304746972314
01/03/20143426731063322
01/04/20144752241046454
01/05/2014420342998421
Grand
  Total		642811317316371

This however doesn't give me the rolling average, it just simply gives me an average of each one month.

This is the results that I want:

01/01/20135257061006523
01/02/20137755131967394
01/03/201310053872686374
01/04/201313940553641383
01/05/201318451054768387
01/06/201322734135894386
01/07/201326689806893387
01/08/201329918067846381
01/09/201333859989034375
01/10/2013378771310135374
01/11/2013410819811233366
01/12/2013456815812253373
01/01/2014435942212231356
01/02/2014441436112242361
01/03/2014452716012586360
01/04/2014461371612677364
01/05/2014458300812548365

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.

The formula i am currently using to try and do this is:

rangeavg(above(



(Sum(SegRevenueInUSD)/Sum(Passengers)



),0,12))

Does anyone know how to solve this?

Let me know if you need anything else..

Many thanks,

Bruno Pereira

Preview file
19 KB
743 Views
0 Likes
1 Solution

Accepted Solutions
Anonymous
Not applicable
‎2014-06-17 01:18 PM
Author

Hello!

You shouldn't average the average. You need to sum the SerRev, sum the Passengers and then divide one by the other. Like this:

RangeSum(Above(Sum(SegRevenueInUSD), 0, 12))/RangeSum(Above(Sum(Passengers), 0, 12))

If you need a total row, use this expression:

(Sum(SegRevenueInUSD) + RangeSum(Above(Sum(SegRevenueInUSD), 1, 11)))/(Sum(Passengers) + RangeSum(Above(Sum(Passengers), 1, 11)))

Hope it helps.

View solution in original post

591 Views
0 Likes
3 Replies
whiteline
Master II
Master II
‎2014-06-17 12:36 PM

Hi.

You can use AsOfTable approach like described here:

Calculating rolling n-period totals, averages or other aggregations

591 Views
0 Likes
Anonymous
Not applicable
‎2014-06-17 01:18 PM
Author

Hello!

You shouldn't average the average. You need to sum the SerRev, sum the Passengers and then divide one by the other. Like this:

RangeSum(Above(Sum(SegRevenueInUSD), 0, 12))/RangeSum(Above(Sum(Passengers), 0, 12))

If you need a total row, use this expression:

(Sum(SegRevenueInUSD) + RangeSum(Above(Sum(SegRevenueInUSD), 1, 11)))/(Sum(Passengers) + RangeSum(Above(Sum(Passengers), 1, 11)))

Hope it helps.

592 Views
0 Likes
Not applicable
‎2014-06-18 05:51 AM
Author

In response to Anonymous

Thank you Bruno!

Nice name too!

591 Views
0 Likes