Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Running diminishing total in Straight table

Hi All

I am trying to calculate a diminishing total within a straight table based on a value as a starting point.

The attached spreadsheet gives an example of what I want to do. I have tried using rangesum below and above.

I can achieve this across the table but want it to calculate down. Not sure if this is possible to achieve.

5 Replies
MVP
MVP

Re: Running diminishing total in Straight table

It should be possible, like

=1197 - rangesum(above( sum([Value to Diminish]),0,rowno()))

in a straight table with dimension Count.

You can replace the constant 1197 as start value with a variable or any other global expression.

Not applicable

Re: Running diminishing total in Straight table

Thanks Swuel

I have used that exact expression but it does not allow for the diminishing total as it should be lowered by the value of 2 for each day.

MVP
MVP

Re: Running diminishing total in Straight table

Not sure if I understand.

In your excel table, there are also values of 100 and 5 to diminish, not only 2 for each day / count.

If your problem is to get the last table row (count 115) as Total in your table and not the starting point, you can use an expression like

=if(Dimensionality() = 0,

     1197- sum([Value to Dimish]),

     1197 - rangesum(above( sum([Value to Diminish]),0,rowno()))

)

Not applicable

Re: Running diminishing total in Straight table

We ended up doing this expression

=If(isnum(above(total column(4))) , above(total column(4))-sum([Forecast Sales K Daily]),  column(2)-column(3))

Capture.PNG

MVP
MVP

Re: Running diminishing total in Straight table

Yes, seems I haven't understood what you are trying to do first place (e.g. first line, calculating 1192 from 1195 using a Forecast of 2).

But good to hear that you've found a solution.

Community Browser