topic Re: Aggr rangesum performance tuning. in QlikView

Aggr rangesum performance tuning.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a situation where there is a date slider (rolling 13 months) and stacked bar charts on dashboard. All these stacked bar charts are using expressions

aggr(rangesum(above(sum({<some filters>}Count_1)),0,rowno()), date_range,category)

aggr(rangesum(above(sum(TOTAL{<some filters>}Count_1)),0,rowno()), date_range,category)

filters are on date_range, flags etc.

This expression is causing my application to hang and on server it takes 7-10 minutes to respond to any change in date slider.

I did this to show cumulative sum view on charts.

My data look like :

Month	Category 1	Category 2	Cumulative Sum Category 1	Cumulative Sum Category 2
Jan 2016	20	70	20	70
Feb 2016	30	120	50	190
Mar 2016	15	18	65	208
Apr 2016	17	23	82	231
May 2016	28	82	110	313
Jun 2016	44	47	154	360

Cumulative sum is not present in my data base, also since i am using slider so i can't pre-calculate it in script and then use on Dashboard.

I know that the only way to handle this is perform these calculation at script level and i tried but messed things. Can anyone share a qvw showing how to carry this in script.

Thanks,

Stabdha

Re: Aggr rangesum performance tuning.

Frank_Hartmann — Sun, 24 Jul 2016 11:29:14 GMT

hi,

try in script as following:

LOAD

Month,

[Category 1],

[Category 2],

RangeSum(Peek(newCat1), [Category 1]) as newCat1,

RangeSum(Peek(newCat2), [Category 2]) as newCat2

FROM

hope this helps

Re: Aggr rangesum performance tuning.

Anonymous — Tue, 26 Jul 2016 13:29:04 GMT

Hi Frank,

Thanks for the solution but my time period is not constant, i have a slider and data should keep changing according to the slider. e.g.

Check for Mar2016 data, cumulative is now depending on the selected time period (Feb 2016 to mar 2016 and not Jan-Mar 2016).

Thanks,

Stabdha

Re: Aggr rangesum performance tuning.

Frank_Hartmann — Tue, 26 Jul 2016 17:50:55 GMT

see attached qvw

hope this helps

Re: Aggr rangesum performance tuning.

Anonymous — Tue, 26 Jul 2016 18:41:36 GMT

Thanks for the solution Frank. I too used similar expression but the issue i mainly faced is of performance. When i change the date range through slider the charts take forever to load. That's why i wanted to somehow calculate the same on script and use it on UI for better performance.