topic Re: Accumulation question in QlikView

Accumulation question

Tue, 08 Jul 2014 02:20:31 GMT

I'm trying to calculate the balance of a balance sheet account at a given period.

I would like to create the values in the second column (Cash PTD). I have the following expressions:

Cash xTD: =Sum({1<Debug={'Actuals'}, AC3={'100'}, PostingDate = {>=$(Min(PostingDate))}>} BalanceLC)

Cash PTD: =Sum({1<Debug={'Actuals'}, AC3={'100'}>} BalanceLC) (and selected "Full Accumulation")

The xTD shows me the balance of that specific period, but not on a cumulative basis. When I try to use the overall Min(Date) by replacing the $ with a 1, I get an error in the expression. So this does not work:

=Sum({1<Debug={'Actuals'}, AC3={'100'}, PostingDate = {>=1(Min(PostingDate))}>} BalanceLC)

How can I select the overall Min(Date) in this set formula to calculate a cumulative value from the very first period up to the given period?

I don't want to use the "Full Accumulation" as the values depend on the sorting of the FPeriod field. If I sort is descending, the values are obviously no longer correct.

Thanks in advance.

Re: Accumulation question

jagan — Tue, 08 Jul 2014 04:10:51 GMT

Hi,

Try Above(), Qlikview calculates at chart and not by row level, so row level you cannot change the date.

Regards,

Jagan.

Re: Accumulation question

jagan — Tue, 08 Jul 2014 04:16:21 GMT

Hi,

PFA file for solution.

Regards,

Jagan.

Re: Accumulation question

Tue, 08 Jul 2014 04:27:01 GMT

Hi Jagan

Thanks for your post, but I don't quite understand the "Try Above()". How or where would I include this?

Unfortunately I can't post the file, as it is with live company data...

Re: Accumulation question

jagan — Tue, 08 Jul 2014 05:39:08 GMT

Hi,

Above() will give you the previous row value in the Straight or Pivot table

=Rangesum(Above([Accum Value], ), Sum(Value))

Regards,

Jagan.

Re: Accumulation question

maleksafa — Tue, 08 Jul 2014 06:02:20 GMT

hi,

check this link

Calculating rolling n-period totals, averages or other aggregations

Re: Accumulation question

maleksafa — Tue, 08 Jul 2014 06:04:27 GMT

Hi,

the below link might be useful

Calculating rolling n-period totals, averages or other aggregations

Re: Accumulation question

Tue, 08 Jul 2014 06:52:06 GMT

Thanks