YTD cumulatives

[qlikhans]

I have a table similar to:

Bookperiod, Bookdate, GLaccount, Debit, Credit

I succeed in creating a pivot table that returns the mutation (=Debit - Credit) per Bookperiod.

account	period 1	period 2	period 3	Header 5	Header 6	Header 7	Header 8
building	1	1	1	etc
inventory	2	1	3

Now I would like to have per Bookperiod the YTD cumulative

account	period 1	period 2	period 3	Header 5	Header 6
building	1	2	3	etc
inventory	2	3	6

how to do that?

thanks

Hans

I have posted a related topic as an idea for future new releases. In case you like this, follow the link and vote.

http://community.qlik.com/ideas/1885

[swuehl]

Hans,

I think you could use chart inter record functions to achieve this:

=rangesum(before((Debit-Credit),0,ColumnNo()))

Have a look at rangesum and before functions in the help file for details. Please note, that you might need to change before() to e.g. above() if you change your dimensions orientation in your pivot table.

Regards,

Stefan

[perumal_41]

Hi.

Please upload ur Qlikview file .

Regards ,

Perumal.A

[qlikhans]

Hi,

The file is uploaded. I do not know whether you can access it, since the file is created in a personal version of QV.

Kind regards

Hans

[qlikhans]

Hi Stefan,

It does not work yet. But you have pointed out a direction that is useful to me.

kind regards

Hans

[swuehl · Accepted solution]

Hi Hans,

yes, we can  open your file with a full version of QV, thanks for uploading.

I think you are almost there, you have to remove the equal sign from the middle of your expression,i.e.

=rangesum(above(sum(Debet)-sum(Credit),0,rowno()))

to get the accumulated sum in vertical direction (across the BAL_GROEP) and

=rangesum(before(sum(Debet)-sum(Credit),0,columnno()))

to get the accumulated sum across the periods.

Hope this helps,

Stefan

[qlikhans]

Perfect!!

It works.

Thank you v.m.

knd rgrds

Hans

[#8]

if you filter by period 2 and period 3, would you still have the correct values accumulated? Or would you have for Building 2 and for inventory 4?

EDIT: i try this and the accumulated value changes with the selections. Do anyone know how to keep the correct value even if you do a selection?