topic Re: Running Total In A Straight Table in QlikView

Running Total In A Straight Table

pnn44794 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Running Total In A Straight Table

pnn44794 — Mon, 26 Jun 2017 20:41:49 GMT

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

Re: Running Total In A Straight Table

MarcoWedel — Mon, 26 Jun 2017 22:28:38 GMT

Hi,

maybe one solution could be:

hope this helps

regards

Marco

Re: Running Total In A Straight Table

pnn44794 — Tue, 27 Jun 2017 13:24:08 GMT

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?

Re: Running Total In A Straight Table

pnn44794 — Sun, 13 Oct 2019 18:07:32 GMT

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.

Re: Running Total In A Straight Table

MarcoWedel — Tue, 27 Jun 2017 22:29:14 GMT

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

Re: Running Total In A Straight Table

pnn44794 — Sun, 13 Oct 2019 18:03:14 GMT

Hello Marco:

Thanks for the quick reply. Wow. Very weird.

Re: Running Total In A Straight Table

MarcoWedel — Wed, 28 Jun 2017 00:14:58 GMT

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

regards

Marco

Re: Running Total In A Straight Table

pnn44794 — Wed, 28 Jun 2017 00:29:29 GMT

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

Re: Running Total In A Straight Table

pnn44794 — Sun, 13 Oct 2019 18:01:31 GMT

Interesting. It almost works as expected.

Re: Running Total In A Straight Table

pnn44794 — Sun, 13 Oct 2019 17:58:10 GMT

Ok. So I've added each field I want to display as an expression using just the field name and nothing else. The only dimension I use is ID. This seems to work. However, the math does not work out. Any ideas?

Re: Running Total In A Straight Table

MarcoWedel — Thu, 29 Jun 2017 23:39:32 GMT

Hi,

maybe you can upload a sample application to work with?

Preparing examples for Upload - Reduction and Data Scrambling

thanks

Marco