Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hi.
Please upload ur Qlikview file .
Regards ,
Perumal.A
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
Hi Stefan,
It does not work yet. But you have pointed out a direction that is useful to me.
kind regards
Hans
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
Perfect!!
It works.
Thank you v.m.
knd rgrds
Hans
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?