Accumulation question

[#1]

Hi

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.

[jagan]

Hi,

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

Regards,

Jagan.

[jagan]

Hi,

PFA file for solution.

Regards,

Jagan.

[#4]

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

[jagan]

Hi,

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

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

Regards,

Jagan.

[maleksafa]

hi,

check this link

Calculating rolling n-period totals, averages or other aggregations

[maleksafa]

Hi,

the below link might be useful

Calculating rolling n-period totals, averages or other aggregations

[#8]

Thanks