topic Re: Calculating Running Total In Pivot Table in QlikView

Calculating Running Total In Pivot Table

asmithids — Sat, 14 Feb 2015 22:54:26 GMT

Hello,

Attached is an app that shows all of the inventory activity for items in each store (i.e. beg bal, sales, transfers in&out, adjustments). I am trying to create a running balance column for each item in each store. I used AGGR in the pivot table to isolate each transaction quantity per store, item and transaction type. I am trying to use AGGR in the pivot table also to calculate the running balance by aggregating the beginning balance of each item per store with the sales, transfer and adjustment activity per store.

I am not seeing where the problem is in calculating the running balance. I tried creating the running balance calculation in the pivot table and in the load script.

Thank you in advance for any assistance with this issue!

Re: Calculating Running Total In Pivot Table

Gysbert_Wassenaar — Mon, 16 Feb 2015 10:39:36 GMT

Perhaps this document helps: Calculating rolling n-period totals, averages or other aggregations

Re: Calculating Running Total In Pivot Table

asmithids — Mon, 16 Feb 2015 15:01:43 GMT

Hi Gysbert,

Thank you the document. It did provide some help. I am using the following calculation as per the document.

Sum(Aggr(rangesum(above(sum(SalesQTY),0,1) ),ItemID,STORE))

The issue now is that the accumulation is doubling the result per ItemID per STORE. See attached image. Any thoughts as to why the result is doubling?

Thank you,

Re: Calculating Running Total In Pivot Table

Miguel_Angel_Baeyens — Tue, 17 Feb 2015 10:53:09 GMT

Hi Alex,

Not 100% sure if this is what you are looking for?

Miguel

Re: Calculating Running Total In Pivot Table

asmithids — Tue, 17 Feb 2015 19:36:34 GMT

Thank you Miguel. We came to the same resolution. The app is working now.

Thank you again!