Running Total In A Straight Table

[pnn44794]

Hello:

I have a straight  where I have a beginning balance which is not a loaded value or field.  I need to decrement that value by the value on each row in fieldname total_costs.

As always, I appreciate any and all responses.  Thanks in advance for your assistance.

[pnn44794]

Ignore my comment about the apostrophe.  Was meant for another window. 

[MarcoWedel]

Hi,

maybe one solution could be:

hope this helps

regards

Marco

[pnn44794]

Hello Marco:

Sorry for the delay in replying.  When I add the expression, Qlikview says there's an "Error in expression" and the dreaded red squiggly lines under the first [Running Balance].  This is probably my fault as I did not mention that Running Balance does not actually exist in the data I pull down.  For the calculation, that's what I would like to call the field.  Do I need to do some kind of inline load or some type of declaration for that field?

[pnn44794]

Hello Marco:

I simply cannot get your solution to work, though I clearly see that you did.  I'm stumped as to why your solution does not work for me and I'm guessing I'm missing something simple again.

I did add RecNo() as ID like you have.  Also, you'll see id as a field.

As an FYI, if you happen to reply tonight, I'm hoping to log back on a little later in the evening though I could get tied up with yard work.    So if you do respond back tonight, I will try to get back to you as quickly as I can.  Thanks.

[MarcoWedel]

Hi,

I guess the short answer is that my expression only works for one dimension.

Your example has multiple dimensions though (that in parts rather look like facts to me and thus might better be suited as expressions?), so adding some total qualifiers to the Above() and RowNo() functions should do the job:

Dual(
Money(If(RowNo(TOTAL)=1,25000000,Above(TOTAL [Running Balance])-[Total Cost])/1000000,'$00.00 M;-$00.00 M'),
  If(RowNo(TOTAL)=1,25000000,Above(TOTAL [Running Balance])-[Total Cost])
)

hope this helps

regards

Marco

[pnn44794]

Hello Marco:

Thanks for the quick reply.  Wow.  Very weird.

[MarcoWedel]

check your field names, guessing from the script you posted, they might be slightly different.

regards

Marco

[pnn44794]

Qlikview is not liking [Total Cost] which is a field I'm reformatting.  I'll try total_costs.

[pnn44794]

Interesting.  It almost works as expected.