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.
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).
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.