Running Balance calculation issue in middle of running balance- resets?

[#1]

Re: Rangesum - Running Balance by Dimension

The link above gave me great help to get my running balance working but when i went to validate, there are 3-4 groupings where the balance "loses its way" - See yellow highlight on Attached Excel

As you can see from the attached QVW, 99.5% of the balances are tracking perfectly except for 3-4 items where it calculates perfect then in the middle, the calculation seems to just "reset".   Odd thing is when I drill into those records (by dates 5/24/16 & 5/25/16, the running balances work.

Another balance error is in Warehouse 120

In the QVW there is a Bookmark for the Drill Down

[michael123]

Hi,

I think your problem comes from using a pivot table and the way it calculates internally. I think you should use "before" instead of "above" if pivot is a must.

If you change it to a straight table it seems to work with your expression.

Have not been able to get it to work with before but perhaps you could search the community for ideas and try more.

Here is one I tried with no success ... how to perform above in pivot table

Cheers!

/Michael

[Anonymous]

For me this looks like a bug, I have no explanation at all...

It is very strange: if you change the pivot to straight table it works!

I don't think before() is needed here instead of above() because there are no horizontal dimensions in the example.

(I tried with QV 11.2 SR15)

[#4]

Considering that it works fine for almost the entire application, and that it calculates fine when you drill down, could some odd sort or hidden "conditional" dimension cause the problem?

I too almost think it is a bug.

[Anonymous · Accepted solution]

I would ask your QlikView Partner or Qlik directly, if you are allowed to.

(Please give feedback, if it is really a bug)

[#6]

Something with the (above TOTAL) maybe?  Something in the underlying sort?

I will submit to support and report back if bug.

[Anonymous]

as long as WA_TranType is one of the dimensions it should work...  you also did not supress zero values, so we should see the problem... awaiting your feedback ...

[#8]

One of the visible dimensions?   Based on one of your last post from the other thread this is the current formula - took out the AGGR() and therefore the dimensions?

if(WA_12mo.WA_TranType='BEG',

  sum(WA_12mo.TotalCost),   

     if(WA_12mo.WA_TranType<>'END',

     RangeSum(

          sum(WA_12mo.TotalCost),

          above (TOTAL  RunBal) //recursive...

         )

     )

)

[Anonymous]

You can take out the aggr(), I just wanted to use it for forcing the dimension order because I first tried to use above() without total...

visible dimension "is" here:

so all is "ok", your expression is correct.

[#10]

It has something to do with Transaction Date.   If I take out the Transaction Date it balances no matter what dimension I put in.

When I put in  Transaction Date, part of the underlying data is broken - There is more data associated with "Product" specifically "Dimension"   ie 4x6, 4x8, 9x7.

So when Dimension is not specified, and the last sort is TranDate, you would get some of the Dimension interleaving the data stream.

What I don't get and why it may be a bug is that there are thousands of rows above and below this portion of the data that have the same behavior but are not acting this way.