topic How to calculate accumulated amount in script? in QlikView

How to calculate accumulated amount in script?

Mon, 30 Aug 2010 17:41:15 GMT

Hi!

YTD-questions seems to be a common question but I can't find a previous solution for my YTD-problem.

I got a table that inclueds Unit, Period and Amount_Month. I like to calculate a new field in the script with Amount_YTD as shown in the table below.

Any ideas?

Miguel_Angel_Baeyens — Mon, 30 Aug 2010 18:54:33 GMT

Hello Kristofer,

Using peek() and previous() functions will help you to do that. My untested guess for your new field is

If(Unit = Previous(Unit), Rangesum(Amount_Month, Peek(Amount_Month))) AS Amount_YTD,

Of course you can add new conditionals to compare with previous periods or previous fields.

Anyway, the more complex your field is, the longer the load will take.

Hope that helps!

Mon, 30 Aug 2010 19:30:02 GMT

Thanx for your answer. Your tip got me on the right track. I had to adjust the last peek to Amount_YTD. This soleved my problem:




If

(Unit = Previous(Unit) and left(Period,4) = previous(left(Period,4)), rangesum(Amount_Month, Peek('Amount_YTD')),Amount_Month) as Amount_YTD