Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
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
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,