topic Re: Aggr function on avg in QlikView

Aggr function on avg

Thu, 30 Jul 2015 19:04:51 GMT

Hi,

I need average of sales at 'geography' and 'level' and want to compare ID sales with that average. I have tried aggr function but it's giving null for some values. Please help.

Regards,

Navdeep

Re: Aggr function on avg

stevedark — Thu, 30 Jul 2015 19:34:21 GMT

Hi Navdeep,

You either need to remove the ID as a dimension from the table, or add ID as a parameter to the AGGR function.

How you have it at the moment where Level and Geography have two IDs; the correct average is being put on one of the rows and the null value is being shown on the other.

Hope that helps.

Steve

Re: Aggr function on avg

Kushal_Chawda — Thu, 30 Jul 2015 19:48:41 GMT

change your avg expression as

avg(total <Geography,Level>Sales)

Please see the attached

Re: Aggr function on avg

stevedark — Thu, 30 Jul 2015 19:54:11 GMT

Thinking about it, whilst you have Level and Geography as dimensions, the aggr function is doing absolutely nothing, you can just do avg(Sales) to get the average.

Aggr functions are only useful if you intend to use the output as a dimension, or if you need to put another expression around it, for example:

sum(aggr(avg(Sales),Geography,Level))

This will give you the total of the averages by those dimensions.

Steve

Re: Aggr function on avg

Fri, 31 Jul 2015 05:30:20 GMT

Hi Kush,

Seems perfect I have some set conditions also, how to include those in this expression and if possible please briefly explain the avg(total <Geography,Level>Sales) expression.

Thanks,

Navdeep

Re: Aggr function on avg

Fri, 31 Jul 2015 05:37:16 GMT

Ok, yes I understand your point, m always little confused about the functionality of aggr(). 🙂

Re: Aggr function on avg

Kushal_Chawda — Fri, 31 Jul 2015 06:21:59 GMT

avg(total <Geography,Level>Sales) - It will return the avg of Sales within selection of each Geography and level.

If you have set expression then please use the solution suggested by Steve.Because due to total qualifier Set expression will not give you accurate results

Re: Aggr function on avg

stevedark — Fri, 31 Jul 2015 07:52:26 GMT

The way to think of AGGR is allowing you to create a temporary table and then do further calculations over it. So, say you wanted the sum of values for field A and B in a table, but as the total you wanted to show the Average of all those rows; you would simply create a table with A and B as dimensions, have your expression as sum(Value) and then change the Total Mode to average. All very simple and no AGGR statements involved.

Now, if you wanted to show the average value in a text box or in the caption of a chart, you would not have a table with all the rows in - so you would need to create a virtual table to work over. The expression avg(aggr(sum(Value), A, B)) will do this for you. Essentially, the table you have in the first example is made in memory, for the average function to work over.

That is the basic premise of AGGR, but it can be used in other scenarios also. One that I use quite frequently is when calculating exceptions and having them emailed out by a QlikView alert. So, perhaps where a Customer has gone over their credit limit. I use an AGGR on the CustomerID with an IF statement in, returning Customer Name for those over the limit and NULL for those under. Around the AGGR I put a Concat DISTINCT statement, that then turns this into a list of items.

Hope that clears it up for you?

Steve