rangesum where the total for the column is the start value for the next

[bert_geraerts]

Hi guys,

I am struggeling to create the following pivot table where the total (= End) for the first column is the Start-value for the second column and so on. 

I have tried different rangesums, but I just cannot figure this one out because the value is always 0.  Can someone help me with the correct syntax?  I have attached the original qvw as an attachement.

The first Start-value should be :   sum(Tankinhoud)

End =  Start + sum(Aanvoer) - sum(VerbruikPEBLIK)

Second column Start = End 1st column

PS:  the reason for the set analyses is to show the dates in the pivot from friday till sundays (10 days) insteat of a normal week.

[edwin]

it may be possible theres a way to do this in the chart using AGGR, ABOVE, and RANGESUM.  but i suggest an alternative solution using a bridge.  link the calendar date to 2 dates: 1 = current date and 2 = prior date.  then in your calculation, add datetype = CURRENT , then add a similar expression for END where datetype=PRIOR and then you get the prior date's totals.  of course if you dont want to see prior day's totals for the first entry, you can add and if statement.  see attached.

[bert_geraerts]

Hi, thanks for that proposal, but the results don't seem right.   I need a running total for it to be correct and I have solved it now by calculating a running total in the loading script.  With the help of a variable I can determine the start of this running total in the selected data range, but I still would like to know the correct formula to do the same in a pivot table and not the script.   

I can do the same with a rangesum when I use a "normal" pivot table with rows, but when I use columns in the pivot table it gives me the wrong or no results.

[Brett_Bleess]

You would need to use Set Analysis I believe, here are some links that may be helpful on that topic:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/QlikView-App-Development/rangesum-where-the-total-for-the-column-is-th...

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Hopefully this may help you sort things out using expressions instead of variables in the script.

Regards,
Brett