topic Cummulative sum of spend comparison with variable in QlikView

Cummulative sum of spend comparison with variable

owais_sarwar — Tue, 17 Apr 2018 05:42:54 GMT

Hello All,

I have one straight table and i need to incorporate an input box so that user could give an input basis the budget requirement. I need that if the use has input 10,000,000, the straight table should show the number of spots/ iterations till the time it is <= to the budget input by the user. As the calculations are on the expression level and as we cannot incorporate the DO while and for next loop in the expression level. Please suggest me a solution relevant to the expression level.

Your help would be highly appreciable.

Regards,

Owais Sarwar.

Re: Cummulative sum of spend comparison with variable

petter — Tue, 17 Apr 2018 05:54:58 GMT

The input by the user can be treated as a constant and as such you can use it in any row level calculations.

Can you clarify what you mean by spots/iterations please?

Do you you have to generate a variable number of rows based on some condition that involves the input from the user?

If you could give us an example on how it would or should look like maybe it's possible for the community to give you good guidance....

Re: Cummulative sum of spend comparison with variable

owais_sarwar — Tue, 17 Apr 2018 06:30:10 GMT

Hello Petter,

Below attached is the picture of the straight table and an input box. I need to implement that if the user input the budget in the input box. The straight table should show the rows till the time it reaches the Sum(Bugdet) <= VBudget. In other words may be i need the running total till the condition reaches.

If have written the set expression with if statement i.e.

=If(Num(Sum([Rate]/2)) < $(VBudget),Num(Sum([Rate]/2),'#,##0'))

But, it is showing the sum of budget <= to the input (VBudget) in each row rather than the total Budget <= Input.

Re: Cummulative sum of spend comparison with variable

petter — Tue, 17 Apr 2018 08:45:54 GMT

RangeSum( Above(TOTAL Sum([Rate]/2) , 0 , RowNo(TOTAL))) <= VBudget

It is important that the table is sorted correctly so it makes totals that make sense. You should turn of dynamic sorting for the table and give a fixed sorting order.

The Above() inter-record function can pick one or more rows above in the chart. It takes three parameters. The expression to calculate over the number of rows that start at the position which the second parameter specify which is 0 (current row) and takes as many rows as the third parameters specify .... RowNo(TOTAL) which returns the current row - which is the number of values you want to sum

Re: Cummulative sum of spend comparison with variable

owais_sarwar — Tue, 17 Apr 2018 10:51:10 GMT

Hello Petter,

Your logic behind the expression is absoutely right. But i am unable to figure out why this expression is still not working. Attached is the screenshot. It has been stuck for so long and i have already closed it forcibly several times. The expression behind this is:

IF(RangeSum( Above(TOTAL Sum([Rate]/2) , 0 , RowNo(TOTAL))) <= VBudget, Num(Sum([Rate]/2)))

Also, as per your instructions, i have unchecked the 'Allow interactive sort' and have only checked numeric on the measure.

I have tested it on a small scale and it was working perfectly.

Please tell me some way out so that i could pursue.