AGGR

[#1]

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

[Anonymous]

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.

[#3]

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

Thanks Michael.

JJ

[#4]

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

[#5 · Accepted solution]

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

[#6]

nice example Bud !

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

regards

JJ

[#7]

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

[#8]

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.

jj

[#9]

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.

[#10]

Hi JJ,

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

Regards,

Rikab