topic Performance issue with aggr function in QlikView

Performance issue with aggr function

diwakarnahata — Thu, 12 Feb 2015 12:01:54 GMT

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

Re: Performance issue with aggr function

rubenmarin — Thu, 12 Feb 2015 12:05:59 GMT

Hi Diwakar, maybe:

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

Re: Performance issue with aggr function

diwakarnahata — Mon, 16 Feb 2015 13:18:10 GMT

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

Re: Performance issue with aggr function

jonathandienst — Mon, 16 Feb 2015 13:24:49 GMT

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

Re: Performance issue with aggr function

rubenmarin — Mon, 16 Feb 2015 13:42:28 GMT

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

Re: Performance issue with aggr function

punitpopli — Fri, 20 Feb 2015 12:16:57 GMT

Hi Diwakar,

I think this might help :

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

Re: Performance issue with aggr function

diwakarnahata — Fri, 20 Feb 2015 12:19:05 GMT

Thanks Punit, it worked perfectly!!!