Problem with aggr

[#1]

Good day friends,

I am having some trouble using an aggr function.

I have this data as below:

Employee	OrderID	Office	City	Time
Jack	1	BRITJ	Itajai	2
John	1	BRPNG	Paranagua	3
Michael	1	BRITJ	Itajai	1
Mark	2	BRNVT	Navegantes	4
Goldman	2	BRITJ	Itajai	2
Kevin	2	BRITJ	Itajai	3
Jane	2	BRNVT	Navegantes	3
Beth	1	BRPNG	Paranagua	2
Richard	1	BRPNG	Paranagua	1
George	1	BRNVT	Navegantes	4

I want the average of time, by OrderID, but only OrderID with the number 2, in this case shall be 3 - avg (4,2,3,3).

So i want an expression, that take each line that OrderID is equal to 1 and compare with this total average of OrderID equal 2.

Example:

Jack = 2/3 = 0,666

John = 3/3 = 1

Michael = 1/3 = 0,333

Beth = 2/3 = 0,666

Richard = 1/3 = 0,333

George = 4/3 = 1,333

To complete my goal, the average of this 6 results shall be 1, otherwise it don't reach the goal.

In this example it gives me the average of 0,72, so it didn't reach the goal.

Do you have any ideas how can i do that?

[matt_crowther]

Jorge,

Solution attached hopefully.

I haven't used an aggr() statement simply a couple of if() statements to control the calculations.

The main expression is: =Time/(Avg(total if(OrderID=2,Time)))

.qvw attached.

All the best,

Matt

Visual Analytics Ltd

Qlikview Design Blog: http://QVDesign.wordpress.com

@QlikviewBI

[#3]

Thanks for your reply Matthew,

Anyway, i've got another trouble

Do you know how can I consider the total average of OrderID = 2, and also into that the average by Office.

In this example BRNVT shall gives me 3.5 and BRITJ 2.5.

But this is a small part of the list, I have over 15 offices, and this is killing me, cause if i use with 'If' i shall give for all these offices the condition, what will turns the expression too big.

Tks and regards,

Jorge Souza

[matt_crowther · Accepted solution]

Jorge,

You could create the Office Average in load script as below:

Data:
LOAD * INLINE [
    Employee, OrderID, Office, City, Time
    Jack, 1, BRITJ, Itajai, 2
    John, 1, BRPNG, Paranagua, 3
    Michael, 1, BRITJ, Itajai, 1
    Mark, 2, BRNVT, Navegantes, 4
    Goldman, 2, BRITJ, Itajai, 2
    Kevin, 2, BRITJ, Itajai, 3
    Jane, 2, BRNVT, Navegantes, 3
    Beth, 1, BRPNG, Paranagua, 2
    Richard, 1, BRPNG, Paranagua, 1
    George, 1, BRNVT, Navegantes, 4
];

Left Join (Data)
Load Office,
avg(Time) as Office_Avg
Resident Data Group By Office;

It's then straight forward to add this into your expressions in the front end.

Hope that helps,

Matt

@QlikviewBI

[#5]

Yes, it helps me a lot.

It didn't cross my mind to use group by, i don't use often this statement.

That's why is good publish in here

Many thanks.

Rgds,