topic Re: aggr function - Taking more time in QlikView

aggr function - Taking more time

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am using below code in my combo chart to calculate YTD Average.

Dimensions: DEPT,PRODUCTTYPE

Selection: MONTH,YEAR

aggr(RangeAvg(Above( sum({<Year={$(=Max(Year))}, MONTH>}SALES),0,RowNo())),DEPT,PRODUCTTYPE,MONTH)

aggr(RangeAvg(Above( sum({<Year={$(=Max(Year))}, MONTH>}VOLUME),0,RowNo())),DEPT,PRODUCTTYPE,MONTH)

Issue: Application is hanging showing green color loading button when I select MONTH.

Please help me in fine tune the expression.

Thanks in advance.

Re: aggr function - Taking more time

vinieme12 — Tue, 14 Feb 2017 09:27:11 GMT

do you really need aggr ?? you already have those dimensions in the chart?

Have you tried without aggr()?

Re: aggr function - Taking more time

vinieme12 — Tue, 14 Feb 2017 09:28:23 GMT

or try below

aggr(

RangeAvg(Above( sum({<Year={$(=Max(Year))}, MONTH>}SALES),0,RowNo()))

RangeAvg(Above( sum({<Year={$(=Max(Year))}, MONTH>}VOLUME),0,RowNo())),DEPT,PRODUCTTYPE,MONTH)

Re: aggr function - Taking more time

Tue, 14 Feb 2017 09:29:59 GMT

Hi vinieme12

Those two DIMENSIONS DEPT,PRODUCTTYPE are in chart. MONTH is the selection.

Shall I remove aggregate function?

Could you give updated expression?

Re: aggr function - Taking more time

vinieme12 — Tue, 14 Feb 2017 09:30:45 GMT

also i think you are averaging the average here? is that exactly what you need?

Normally

RangeSum(Above( sum({<Year={$(=Max(Year))}, MONTH>}SALES),0,RowNo()))

RangeSum(Above( sum({<Year={$(=Max(Year))}, MONTH>}VOLUME),0,RowNo()))

Average – Which average?

Re: aggr function - Taking more time

vinieme12 — Tue, 14 Feb 2017 09:31:42 GMT

have you tried after removing Aggr() ?? also are you sure you are doing the correct average? check my replies below

Re: aggr function - Taking more time

Tue, 14 Feb 2017 09:34:27 GMT

I want YTD average of SALES/VOLUME.

Re: aggr function - Taking more time

vinieme12 — Tue, 14 Feb 2017 09:44:38 GMT

Buddy can't help you much with that , you should know what results you want.

I can only help you if the results are not as expected.

Re: aggr function - Taking more time

Tue, 14 Feb 2017 09:44:44 GMT

I will try and let you know.

Re: aggr function - Taking more time

Tue, 14 Feb 2017 10:00:36 GMT

Please find the sample data.

Re: aggr function - Taking more time

sunny_talwar — Tue, 14 Feb 2017 10:25:16 GMT

I would suggest using The As-Of Table‌ instead RangeSum(Above()) to improve performance here.

Re: aggr function - Taking more time

Tue, 14 Feb 2017 10:54:32 GMT

Hi Sunny,

Could you please help me with the code. How to do that.

I need to calculate YTD AVERAGE.

Thanks in advance.

Re: aggr function - Taking more time

sunny_talwar — Tue, 14 Feb 2017 12:44:57 GMT

Not sure what the expected output is, but can you check the attached