10 Replies Latest reply: Jan 25, 2010 12:31 PM by Jean-Jacques Jesua

# AGGR

Hi

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

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

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

Thanks Michael.

JJ

• ###### AGGR

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

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

nice example Bud !

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

regards

JJ

• ###### Re. :Re: AGGR

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

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

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.

jj

• ###### Re. :Re: AGGR

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

Hi JJ,

Regards,

Rikab

• ###### Re. :Re: AGGR

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 dimensionSecond, 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 salesThen 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 otherwiseThen you count the number of "1" . The count function deals with your dimension, the year in your case.Hope to help youJJ`