topic AGGR Function Problems with 2 expressions or 2 dimensions in QlikView

AGGR Function Problems with 2 expressions or 2 dimensions

Tue, 09 Jul 2013 16:09:19 GMT

Hello,

i try to solve following problem with the following dataset. the data produces duplicates with an counter. That is the example dataset:

Order	Date	Counter	Value
A	01.01.2013	1	10
A	02.01.2013	2	10
A	03.01.2013	3	10
A	04.01.2013	4	10
B	01.01.2013	1	15
B	02.01.2013	2	15
B	03.01.2013	3	15
C	01.01.2013	1	20
C	02.01.2013	2	20

To get the value of the orders i use following formula, which is correct:

sum ( aggr ( max (Value),Order)) = 45

No there is the problem. I want to build a graph (table or pie chart) which shows the MAX Counter and the Value of it. The Formula

sum ( aggr ( max (Value),Order, Counter )) doesn´t show the correct results. It should be like:

Counter Value

4 = 10

3 = 15

2 = 20

How can i use the AGGR Formula with 2 expressions (max (Value)) and (max (Counter)).

PLEASE is there anybody with an idea?

Kind regards

jezze

Re: AGGR Function Problems with 2 expressions or 2 dimensions

Gysbert_Wassenaar — Tue, 09 Jul 2013 16:21:44 GMT

Use Order as dimension and as expressions max(Counter) and max(Value). You don't need the aggr function since you're using only one aggregation function. Because Order is used as dimension the expressions will be aggregated for A, B and C.

Re: AGGR Function Problems with 2 expressions or 2 dimensions

Wed, 10 Jul 2013 07:03:06 GMT

Hi Gysbert,

thank you for your fast reply and sorry, i didn´t exactly understand.

In the example data set, there can be a lot of orders with counter 4. Therefore i thought to use sum(aggr... ) formula. e.g.:

Counter | Value

4 | 100

(100 -> 10(A) + 30 (F) + 50(G) + 10(Z))

how can is use expressions max(counter) and max(value) as you described? Follwing formula is not working:

sum ( aggr ( max (Value) and max(Counter),Order ))