Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Accumulate a value(s) with a condition

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:

Screen Shot 2013-10-08 at 5.26.57 PM.png

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Accumulate a value(s) with a condition

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

5 Replies
MVP
MVP

Re: Accumulate a value(s) with a condition

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

Not applicable

Re: Accumulate a value(s) with a condition

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

MVP
MVP

Re: Accumulate a value(s) with a condition

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

Not applicable

Re: Re: Accumulate a value(s) with a condition

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

Not applicable

Re: Re: Accumulate a value(s) with a condition

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

Order by Template, Day asc;


Best,


Matt

Community Browser