Performance issue with aggr function

[diwakarnahata]

Hi All,

I am facing some severe performance issues while using aggr function in a straight table.

I am calculating a Product Share column in its Category when the Product is selected using aggr function as below:

Sum(Sales) / aggr(NODISTINCT Sum(Sales),Product_Category)

After putting this expression, the RAM consumption spikes up exponentially and consumes all the available RAM on my local.

I am developing on my local with a reduced data of 40 MB, and without this expression the total RAM consumption is 300 MB.

After adding this expression, the RAM consumption goes to 2500 MB and i have to restart my system.

Are there any alternatives how we can rewrite this expression.

Regards,

Diwakar

[rubenmarin]

Hi Diwakar, maybe:

Sum(Sales) /Sum(TOTAL <Product_Category> Sales)

[diwakarnahata]

Hi Ruben,

Thanks for the reply.

I need to put some filter conditions like WeekID={'>$(=max(WeekId)-52)'},Product_SubCategory=, etc. before the TOTAL <Product_Category> expression. How should the final expression be?

The AGGR expression was like:

Sum(Sales) /

aggr(NODISTINCT Sum({$<Product_SubCategory=,WeekID={'>$(=max(WeekId)-52)'}}>} Sales),Product_Category)

So, the WHERE condition filters are to be applied before it is aggregated using TOTAL or AGGR.

Regards,

Diwakar

[jonathandienst]

Hi

You are using Aggr() incorrectly. Aggr() returns a array of values which is usually fed directly to an aggregation function , like Sum() or Max(). QV is getting confused by the expression that you have supplied and is running out of memory while trying.

Ruben's answer is correct for your original post, but now you have changed your requirement from the original post and this is a different question, which should be in new thread.

Jonathan

[rubenmarin]

Hi Diwakar, you can add the 'where' condition with the TOTAL qualifier:

Sum(Sales) /Sum(TOTAL <Product_Category> {$<Product_SubCategory=,WeekID={'>$(=max(WeekId)-52)'}}>}  Sales)

As Jonathan saids, aggr returns more than one vuale (one for each Product_Category in this case), if you want to use aggr it can be something like:

Sum(Sales) /

Sum(aggr(NODISTINCT Sum({$<Product_SubCategory=,WeekID={'>$(=max(WeekId)-52)'}}>} Sales),Product_Category))

[punitpopli · Accepted solution]

Hi Diwakar,

I think this might help :

Sum({<Product_Category=P(Product_Category),Product_SubCategory=,WeekId={'>$(=max(WeekId)-(52))'}>} Sales)

[diwakarnahata]

Thanks Punit, it worked perfectly!!!