topic AGGR Function in QlikView

AGGR Function

Wed, 02 Mar 2011 20:07:51 GMT

Hi,

I have a problem that is tying me up in knots and which I think the AGGR function will resolve, but I just can't quite get it to work.

I want to plot on a chart how a selected store is performing against, stores of a similar type, stores in a similar geographical area and all stores in the estate. So for each date I will show four bars:

· Actual sales for selected store

· Average sales for stores in geo area

· Average sales for stores of similar type

· Average sales for all stores

I believe the expression for the selected store looks like this:

AGGR(SUM({$< StoreName = {"$(=GetFieldSelections(StoreName))"} >} SalesValue ), Date_WkNo)

However, what I have for the other three expressions is returning weird results. For example my geographical comparison expression below returns the same result as that above:

AVG(AGGR(SUM({$< StoreGroup = {"$(=MAXSTRING(StoreGroup))"}, StoreName = >} SalesValue), StoreGroup, StoreName, Date_WkNo))

The logic behind what I'm trying to do here is to find the group for the selected store and calculate the average total sales, by date, for the stores in that group. My expression for the stores of a similar format is very similar to the group one above but my expression for returning the average sales for all stores is this, which also returns the same result as the others:

AVG(AGGR(SUM({$< StoreName = >} SalesValue ), StoreName, Date_WkNo))

Any help in resolving this problem would be appreciated.

Thanks,

Graeme

AGGR Function

Wed, 02 Mar 2011 20:18:35 GMT

Hi there, the issue I found here is that the last aggregation you are using does not contain any set analysis at all, which means that it will remain with the user actual selections, for instance, the following expression should return the result you are looking for:

AVG(({$< StoreGroup = {"$(=MAXSTRING(StoreGroup))"}, StoreName = >} AGGR(SUM({$< StoreGroup = {"$(=MAXSTRING(StoreGroup))"}, StoreName = >} SalesValue), StoreGroup, StoreName, Date_WkNo))

I think in most cases it will be enough to use:

AVG(({$< StoreGroup = {"$(=MAXSTRING(StoreGroup))"}, StoreName = >} SalesValue)

However, it will all depend on the dimension you have within the graphic.

Regards.

AGGR Function

Wed, 02 Mar 2011 21:29:27 GMT

Hi Ivan,

Thanks for your reply. The problem i've had with

AVG(({$< StoreGroup = {"$(=MAXSTRING(StoreGroup))"}, StoreName = >} SalesValue)

is that it gives me the average transaction value across the stores whereas I'm after the average of the sum of all transactions for each store. I think that's why I need to use the AGGR function in this case.

That said, your first example has worked a treat so thank you for your help.

AVG(({$< StoreGroup = {"$(=MAXSTRING(StoreGroup))"}, StoreName = >} AGGR(SUM({$< StoreGroup = {"$(=MAXSTRING(StoreGroup))"}, StoreName = >} SalesValue), StoreGroup, StoreName, Date_WkNo))

Thanks,

Graeme