topic Re: Carrying over prior months data to current month in Set Analysis Pivot Table in QlikView

Carrying over prior months data to current month in Set Analysis Pivot Table

aetingu12 — Sat, 16 Nov 2024 03:44:33 GMT

All,

I have read some examples on cumulative and rolling balances on this, but I was wondering if it was possible to have a pivot table and within the pivot table have a row that displays prior months closing balance.

So in March 19; Opening Balance should be Feb 19's closing balance. I have attached a sample with data.

Help is much appreciated.

Re: Carrying over prior months data to current month in Set Analysis Pivot Table

sunny_talwar — Fri, 26 Apr 2019 12:32:30 GMT

If we do this for March, why don't we get the Jan's closing balance of 7,952.84 as Feb's opening balance? Why do we have a different number for Feb's opening of 7,889.46... which also equals Feb's closing?

Re: Carrying over prior months data to current month in Set Analysis Pivot Table

aetingu12 — Fri, 26 Apr 2019 13:10:14 GMT

That is the problem. That was my trial. I would like to get the prior months closing as current months opening for all dates, unless it is the first date.

Re: Carrying over prior months data to current month in Set Analysis Pivot Table

sunny_talwar — Fri, 26 Apr 2019 13:31:29 GMT

But the opening for Feb is hard coded in the script... I am a little confused.

Re: Carrying over prior months data to current month in Set Analysis Pivot Table

aetingu12 — Fri, 26 Apr 2019 13:40:10 GMT

Ah, yes sorry. I was just trying to illustrate what I wanted. I have attached updated test file with no open date. Basically, I would like to have a new row that displays the closing date from the prior period.

Thanks for your patience.

Re: Carrying over prior months data to current month in Set Analysis Pivot Table

sunny_talwar — Fri, 26 Apr 2019 13:54:19 GMT

Check out the attached

Expression used

If(Only(TOTAL <Movements> Movements) = 'Opening' and Len(Trim(Only(TOTAL <Dates> Dates))) > 0, Before(Sum(TOTAL <Dates> {<Movements = {'Closing'}>}Balances)), Sum(Balances))

Re: Carrying over prior months data to current month in Set Analysis Pivot Table

aetingu12 — Fri, 26 Apr 2019 14:35:20 GMT

Much appreciated!