Accumulate a value(s) with a condition

[#1]

Hi,

I'm trying to create a field that accumulates a value based on the following conditions. If on any give day, revenue equals 0, that days traffic is passed along to the following day where revenue is greater than 0. The table below illustrates the objectives:

The Accumlated Traffic field is what I'd like to create. So, on days 2, 3 and 8, there was no Revenue and these days traffic figures were summed and added to the total of the next day with revenue > 0.

I've tried to achieve this using the peek function on import. I can accumulate traffic where revenue equals 0, but cannot find a way to pass the accumlated value to the next day where revenues greater than 0.

Any help is welcome. I've attached a qvw with the above example and what I've scripted so far.

Best,

Matt

[swuehl · Accepted solution]

I haven't understood the acc. traffic for days 8/9, but maybe like attached?

[#3]

Thanks Swuel, that works. Disregard day 8 above, it should read 0 and day 9 should read 200.

Could you walk me through how this line of the import works? That would be a great help:

Rangesum(Traffic,peek([Traffic Accumulator Temp])),0) as [Traffic Accumulator]

Best,

Matt

[swuehl]

I basically used your created table to start, just renamed Traffic Accumulator to a temp field.

Then I added a preceding load (i.e. I input the table you created, like a pipe)

LOAD *, if(Revenue > 0, Rangesum(Traffic,peek([Traffic Accumulator Temp])),0) as [Traffic Accumulator];

For all lines with Revenue larger than zero, sum this line's Traffic with the accumulated Traffic temp field from the previous line (Rangesum is more or less just adding Traffic + peek([Traffic Accumulator Temp]) , but rangesum() is more robust when one of the operands is NULL (then it takes this value as zero), while a simple arithmetic addition returns NULL for e.g. 10 + NULL).

[#5]

Thanks Swuel, very clear.

Question: in my actual application, I will need to accumulate over another dimension called template. On the same day, one template may have 0 revenue while another will have greater than 0. So this accumulator will need to take another dimension into account.

I've imported some dummy data, but since I'm ordering by Day, I accumulate over Day, however, I need the script to differentiate between Day and Template. I tried adding an aggr to the peek with no luck.

Any idea how to accomplish this? I've attached updated example.

Thanks again for help.

Matt

[#6]

Never mind, I was able to resolve by ordering by template and day.

Order by Template, Day asc;

Best,

Matt