Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
So I have this following data:
Origin | Destination | Passengers | SegRevenueInUSD | Period | |
LDN | NYK | 406 | 61306 | 01/01/2013 | 151.00 |
LDN | NYK | 556 | 81732 | 01/02/2013 | 147.00 |
LDN | NYK | 298 | 38740 | 01/03/2013 | 130.00 |
LDN | NYK | 394 | 53190 | 01/04/2013 | 135.00 |
LDN | NYK | 563 | 84450 | 01/05/2013 | 150.00 |
LDN | NYK | 630 | 94500 | 01/06/2013 | 150.00 |
LDN | NYK | 575 | 74175 | 01/07/2013 | 129.00 |
LDN | NYK | 372 | 52080 | 01/08/2013 | 140.00 |
LDN | NYK | 596 | 76288 | 01/09/2013 | 128.00 |
LDN | NYK | 552 | 72864 | 01/10/2013 | 132.00 |
LDN | NYK | 637 | 77077 | 01/11/2013 | 121.00 |
LDN | NYK | 480 | 61440 | 01/12/2013 | 128.00 |
LDN | NYK | 389 | 48625 | 01/01/2014 | 125.00 |
LDN | NYK | 530 | 75790 | 01/02/2014 | 143.00 |
LDN | NYK | 476 | 68544 | 01/03/2014 | 144.00 |
LDN | NYK | 554 | 83100 | 01/04/2014 | 150.00 |
LDN | NYK | 552 | 68448 | 01/05/2014 | 124.00 |
LDN | ASF | 600 | 464400 | 01/01/2013 | 774.00 |
LDN | ASF | 405 | 168075 | 01/02/2013 | 415.00 |
LDN | ASF | 421 | 191134 | 01/03/2013 | 454.00 |
LDN | ASF | 561 | 335478 | 01/04/2013 | 598.00 |
LDN | ASF | 564 | 366600 | 01/05/2013 | 650.00 |
LDN | ASF | 496 | 333808 | 01/06/2013 | 673.00 |
LDN | ASF | 424 | 321392 | 01/07/2013 | 758.00 |
LDN | ASF | 581 | 270746 | 01/08/2013 | 466.00 |
LDN | ASF | 592 | 317904 | 01/09/2013 | 537.00 |
LDN | ASF | 549 | 328851 | 01/10/2013 | 599.00 |
LDN | ASF | 461 | 243408 | 01/11/2013 | 528.00 |
LDN | ASF | 540 | 398520 | 01/12/2013 | 738.00 |
LDN | ASF | 595 | 268345 | 01/01/2014 | 451.00 |
LDN | ASF | 442 | 228956 | 01/02/2014 | 518.00 |
LDN | ASF | 587 | 274129 | 01/03/2014 | 467.00 |
LDN | ASF | 492 | 392124 | 01/04/2014 | 797.00 |
LDN | ASF | 446 | 351894 | 01/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 Labels | Sum of SegRevenueInUSD | Sum of Passengers | Sum of AverageYieldInUSD |
01/01/2013 | 525706 | 1006 | 523 |
01/02/2013 | 249807 | 961 | 260 |
01/03/2013 | 229874 | 719 | 320 |
01/04/2013 | 388668 | 955 | 407 |
01/05/2013 | 451050 | 1127 | 400 |
01/06/2013 | 428308 | 1126 | 380 |
01/07/2013 | 395567 | 999 | 396 |
01/08/2013 | 322826 | 953 | 339 |
01/09/2013 | 394192 | 1188 | 332 |
01/10/2013 | 401715 | 1101 | 365 |
01/11/2013 | 320485 | 1098 | 292 |
01/12/2013 | 459960 | 1020 | 451 |
01/01/2014 | 316970 | 984 | 322 |
01/02/2014 | 304746 | 972 | 314 |
01/03/2014 | 342673 | 1063 | 322 |
01/04/2014 | 475224 | 1046 | 454 |
01/05/2014 | 420342 | 998 | 421 |
Grand Total | 6428113 | 17316 | 371 |
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/2013 | 525706 | 1006 | 523 |
01/02/2013 | 775513 | 1967 | 394 |
01/03/2013 | 1005387 | 2686 | 374 |
01/04/2013 | 1394055 | 3641 | 383 |
01/05/2013 | 1845105 | 4768 | 387 |
01/06/2013 | 2273413 | 5894 | 386 |
01/07/2013 | 2668980 | 6893 | 387 |
01/08/2013 | 2991806 | 7846 | 381 |
01/09/2013 | 3385998 | 9034 | 375 |
01/10/2013 | 3787713 | 10135 | 374 |
01/11/2013 | 4108198 | 11233 | 366 |
01/12/2013 | 4568158 | 12253 | 373 |
01/01/2014 | 4359422 | 12231 | 356 |
01/02/2014 | 4414361 | 12242 | 361 |
01/03/2014 | 4527160 | 12586 | 360 |
01/04/2014 | 4613716 | 12677 | 364 |
01/05/2014 | 4583008 | 12548 | 365 |
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
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.
Hi.
You can use AsOfTable approach like described here:
Calculating rolling n-period totals, averages or other aggregations
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.
Thank you Bruno!
Nice name too!