Cummulative Sum or Booking curve

[#1]

I am looking to do a cummulative sum of data over a time-type dimension. Commonly called a booking curve.

Table Data:

DaystoDeparture DepDate Sold
--------------------------------------------------
30 09/18 2
20 09/18 1
10 09/18 4
5 09/18 4
1 09/18 5

I want to display:

DaystoDeparture Tot_Sold
---------------------------------------------
30 2
20 3
10 7
5 11
1 16

So i need to sum up for all "DaytoDeparture" and above.

Any help appreciated

[johnw]

I think that full accumulation would do the trick. Chart Properties -> Expressions -> Accumulation -> Full Accumulation. If that isn't quite what you wanted, I'm sure we can come up with an expression to do the job.

[#3]

Hi John,

That worked for the Graph to display. 2 follow-ups if you have a second.

1) The graph X-Axis is 0 to 365. I would like it to display 365 to 0, with the large sum at the '0'. I tried using the "Sort" but it reversed the sort.

2) Is it possible to do upon loading so i can create a table or object that displays the data? I am looking to put a chart on top of the data.

thanks,

Bernard

[johnw]

I'm not sure I understand the question, but here is your example data and numbers displayed as I think you're requesting, with 30 days to departure at the top or left, and 1 day to departure at the bottom or right.

You can accumulate the sum in the load as well, if you prefer. I've put in that option as well. I'm not sure what you mean by "looking to put a chart on top of the data" though.

[#5]

Hi John,

i got it to work by adding a '-' before the daystoDeparture in the Dimension, which makes sense.

Now my X-axis is -365....-60..-30...0 and it sums correctly i.e. '0 Daystodeparture' is a sum of all bookings and -365 is booking on that day.

It terms of "Chart on top of data". I want a page with a graph on top, with the data that is displayed in the graph in a grid chart right below it.

How would I accumulate upon load. i.e. Load the Xactions, then use another "Load' statement to create a QV table that had the cummulate data like the graph.

thanks,

Bernard

[Oleg_Troyansky]

Bernard,

look at functions pick() and previous(). Using those two functions, you can aggregate your data in the load script, fetching the running total from the previous line and adding the current line's amount to the running total. I'm pretty sure there was another thread where a similar problem was discussed - please look it up.

cheers,

Oleg

[#7]

Hi Again,
Sorry I am a little dense, but I could not find the Pick() + Previous() entry int the Forums. I have tried the previous() which worked when i had a single flight on a single date. But i was looking to create this by flight/date.

Flight DaystoDeparture DepDate Sold
--------------------------------------------------

SK100 30 09/18 1
SK100 30 09/18 1
SK100 20 09/18 1
SK100 10 09/18 4
SK100 5 09/18 4
SK100 1 09/18 5
DL100 30 09/18 4
DL100 20 09/18 2
DL100 10 09/18 8
DL100 5 09/18 8
DL100 1 09/18 10

I want to display:

Flight Date DaystoDeparture Tot_Sold
------------------------------------------------------------------
SK100 09/18 30 2
SK100 09/18 20 3
SK100 09/18 10 7
SK100 09/18 5 11
SK100 09/18 1 16

DL100.....

Any help appreciated!

Bernard