Replacement to Aggregate Function

[Shiva123]

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)

                                                                                    )

[Shiva123]

any idea stalwar1‌

[sunny_talwar]

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.

[Shiva123]

sure i will try this ....

[mikevwang]

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')