topic Re. :Re: AGGR in QlikView

AGGR

Fri, 27 Nov 2009 04:17:30 GMT

I have some difficulies to understand well the AGGR( ) function, which is very useful as I saw in the forum

Is anybody can give me some example to be more confortable with or some argument.

Thanks again for your time.

Jean-Jacques

AGGR

Anonymous — Fri, 27 Nov 2009 05:26:59 GMT

Jean-Jacques,
There could be many very different situation. Only one example:
There are two tables:


Master:
 SalesId,
 SalesDate,
 Amount,
 ...
FROM...;
Details:
 SalesId,
 Product,
 ...
FROM ...;

You can see that the relation is one (Master record) to many (Details records). You certainly can join into one table, but it will mutliply the number of records.
In case you don't have to join them, the expression sum(Amount) will work fine. But if for any reason you have to join, the result will be incorrect. It can be corrected by using sum(aggr(only(Amount), SalesId)). No matte rhow many times the same SalesId appear in the table, it's Amoun twill be used only once in the sum().
Apparently the example is not typical, just for illustration.

Re. :Re: AGGR

Fri, 27 Nov 2009 15:45:19 GMT

Yep ! i understand why i don't have the need to use AGGR, it's because i never joined table.

Thanks Michael.

AGGR

Tue, 01 Dec 2009 15:59:03 GMT

Hi,

So what about if you did not join the tables and you want to plot the average amount of Detail records per Master record. (E.g. Average Products per SalesId.) Would the expression be "avg(aggr(count(Product), SalesId))" or what would that give me?

/Niclas

AGGR

Tue, 01 Dec 2009 16:57:38 GMT

I use aggr function when i have to simulate where clause in a graph.

For example: Display a graph with articles more than 5 of quantity.

You have to perform an expression ( for example sum(ITEM_QTY) ) and decide if your article should taken or not. Tha aggr function let you to bring this decision.

In the graph you have also to check that you don't want to display null value dimensions.

See this example

Hope this help

Re. :Re: AGGR

Wed, 02 Dec 2009 02:59:33 GMT

nice example Bud !

it's better than to create a flag as i did.

regards

Re. :Re: AGGR

Sun, 17 Jan 2010 08:25:26 GMT

Hi Bud,

I am also facing problem in understanding about aggr. Can you please explain me how the below code behaves as i don't have any clue on how it works. For your reference i have attached the same document which i downloaded for understanding about aggr. Hope you can provide me the solution.

count(if(aggr(sum(sales), year, company)>iDiffValue,1))

Re. :Re: AGGR

Mon, 18 Jan 2010 18:40:13 GMT

Hi,

Now I think AGGR is the best function in QV. With this, you don't need to aggregate the data outside of QV.

In the example you mentionned you have 3 steps ;:

1) for each year and for each company, you sum the sales => A

2) if A > threshold , you count 1 => B

3) a after you count the number of B

Hope to help you.

Look at this example with 2 AGGR !

= Sum( Aggr( Max( aggr( sum(Close_Hour-Open_Hour)*24 , pcbur , Period) ) , pcbur) )

I just want to calculate the how many hours all the stores (pcbur) of an area (my dimension) are open by week.

regards.

Re. :Re: AGGR

Fri, 22 Jan 2010 08:36:56 GMT

Hi JJ,

I am totally confused here. Still i am not able to understand anything :-(. Could you please work it on in my document so that it is understandable for me. Also requesting you to explain it with some sample data also.

Re. :Re: AGGR

Mon, 25 Jan 2010 11:30:36 GMT

Hi JJ,

I am waiting for your reply. Requirement is very urgent please help me out.

Regards,

Rikab

Re. :Re: AGGR

Mon, 25 Jan 2010 17:31:50 GMT

Hi Rikak

I don't understand what you don't understand in the formula

count(if(aggr(sum(sales), year, company)>iDiffValue,1))


Let's try step by step,

First, you want a chart with year as dimension
Second, you want to count hom many company each year has a sum of sale > IdiffValue (it's your threshold).
To do this directly in the chart, you calculate aggr(sum(sales), year, company)
 => it's the calculation by year & by company of the sales

Then you have to create a flag (1 or 0)
 as if( aggr(sum(sales), year, company) > iDiffValue, 1 ) = 1 if aggr(sum(sales), year, company) > iDiffValue
 = Missing otherwise

Then you count the number of "1" . The count function deals with your dimension, the year in your case.

Hope to help you

JJ