Discussion Board for collaboration on QlikView Scripting.
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.
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.
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.
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()))
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))
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.