topic rangesum where the total for the column is the start value for the next in QlikView

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

bert_geraerts — Wed, 23 Sep 2020 16:08:19 GMT

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.

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

edwin — Fri, 25 Sep 2020 14:30:28 GMT

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.

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

bert_geraerts — Tue, 29 Sep 2020 07:36:01 GMT

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.

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

Brett_Bleess — Tue, 20 Oct 2020 14:34:05 GMT

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-the-start-value-for/m-p/1746516#M453336

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

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

Regards,
Brett