topic Re: Replacement to Aggregate Function in QlikView

Replacement to Aggregate Function

Shiva123 — Tue, 23 Oct 2018 20:22:57 GMT

Hello,

Everyone this is the same point i have been circling about.I have an aggr function in my expressions and they are making the dashboard to perform slow and sometimes the dashboard crashes. Is there any way to replace aggr function so that the dashboard would perform better . i tried total function and it didn't work . The result which is obtained by using aggr function is absolutely correct .

ex of my expression

SUM(AGGR(

Num(((SUM({$<Period={">=$(=MAX(quarter_num)-8)"},Category={"Revenue","Adjusted Revenue"}>}Net_amount)*(-1))/%_Divider),'#,##0.0')

SUM({<Sub_cateorgy = {$(=If(GetFIeldSelections(Sub_cateorgy )>0,Chr(34)&Concat(Sub_cateorgy ,chr(34)&','&chr(34))&chr(34),'*'))}>} Ratio)

,dim 1 , dim2 , dim 3, dim 4)

)

Re: Replacement to Aggregate Function

sunny_talwar — Thu, 25 Oct 2018 18:58:33 GMT

May be move part of the calculation (whatever you can) to the script. Alternatively, you can perform the whole thing in the script, but then you loose the flexibility of getting the information based on change of selections.

Re: Replacement to Aggregate Function

Shiva123 — Thu, 25 Oct 2018 19:01:47 GMT

yes exactly i thought the same to move the upper part , but they are like different categories.

Re: Replacement to Aggregate Function

sunny_talwar — Thu, 25 Oct 2018 19:05:08 GMT

Different categories? What do you mean?

Re: Replacement to Aggregate Function

Shiva123 — Thu, 25 Oct 2018 20:37:12 GMT

i mean .. the values for the categories are many ex : 1 exp : category ={'rev,adj rev'} 2. exp same expression bu t category={'sales,expenses'} like this...

Re: Replacement to Aggregate Function

sunny_talwar — Fri, 26 Oct 2018 00:51:32 GMT

okay then what is the issue? I am not sure what you are looking for if you know what to try?

Re: Replacement to Aggregate Function

Shiva123 — Fri, 26 Oct 2018 17:26:27 GMT

HI,

sunny expression works fine but the thing is that i want to replace aggr function in the expression , because its kind of crashing the dashboard , is there any way to replace aggr with any other function .

Thanks

Re: Replacement to Aggregate Function

sunny_talwar — Fri, 26 Oct 2018 17:45:57 GMT

Can you share an app where you have the Aggr() function used?

Re: Replacement to Aggregate Function

Shiva123 — Fri, 26 Oct 2018 17:47:05 GMT

sure

Re: Replacement to Aggregate Function

Shiva123 — Fri, 26 Oct 2018 20:29:01 GMT

stalwar1‌ here is the app ..

Re: Replacement to Aggregate Function

Shiva123 — Tue, 30 Oct 2018 18:37:21 GMT

any idea stalwar1‌

Re: Replacement to Aggregate Function

sunny_talwar — Tue, 30 Oct 2018 18:43:51 GMT

The problem is because you have multiple Aggr() functions, and I am not sure it would be easy to suggest any change. One thing you can try is something which is mentioned here by rwunderlich‌... Does data sort order impact chart calc time? But what you are really trying to resolve will have to be done in iteration where you try different things and see if they improve performance. One such change you can make is to replace this {<Sub_cateorgy = {$(=If(GetFIeldSelections(Sub_cateorgy)>0, Chr(34) & Concat(Sub_cateorgy, Chr(34) & ',' & Chr(34)) & Chr(34), '*'))}>} with this {<Sub_cateorgy = p(Sub_cateorgy)>} as they seem to be doing the same thing and p() would be more efficient compared to the whole calculation that you have.

Re: Replacement to Aggregate Function

Shiva123 — Tue, 30 Oct 2018 18:53:44 GMT

sure i will try this ....

Re: Replacement to Aggregate Function

mikevwang — Wed, 31 Oct 2018 03:27:27 GMT

It looks like you are using Ratio to allocate Net_amount across Market. I don't think you need the AGGR function at all. Just multiply Ratio inside your SUM.

Num(((SUM({$<Period={">=$(=MAX(quarter_num)-8)"},Category={"Revenue","Adjusted Revenue"}>}Net_amount*Ratio)*(-1))/%_Divider),'#,##0.0')