Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all
I have a table as such:
OrderNumber | Dept | SalesPerson | Score_A | Score_B | Score_C |
O_1 | Clothing | Chris | 4 | 4 | 4 |
O_2 | Clothing | Janet | 4 | 4 | 4 |
O_3 | Shoes | Terry | 4 | 4 | 4 |
O_4 | Shoes | Terry | 2 | 3 | 2 |
O_4 | Clothing | Terry | 2 | 3 | 2 |
O_5 | Urethane | Adam | 4 | 2 | 3 |
What will be the average of Score_A disregarding Dept because for OrderNumber O_4 it has 2 entries.
(The sum of all Score_A, considering Distinct for O_4 is 18. Dividing that by 5 distinct OrderNumber yields 3.6 which is the correct answer)
I tried these:
Using set analysis : =Sum({< Dept= >}Score_A) / Count(Distinct OrderNumber) yields 3.333 which is incorrect.
Using set analysis : =Avg({< Dept= >}Score_A) yields 3.333 which is incorrect.
How can I calculate disregarding the Dept ?
Thanks
Josh
Dear Josh,
Kindly find attached document for proper average calculation.
Please have a look into given below article for average,
Kind regards,
Ishfaque Ahmed
Hi,
Change your script like below
Temp:
LOAD
*
FROM DataSource;
Order:
LOAD DISTINCT
OrderNumber, Dept, SalesPerson
RESIDENT Temp;
Details:
LOAD DISTINCT
OrderNumber, Score_A, Score_B, Score_C
RESIDENT Temp;
DROP TABLE Temp;
Now use below expression
=Sum(Score_A) / Count(Distinct OrderNumber)
OR
=Avg(Score_A)
Hope this helps you.
Regards,
Jagan.
I have done the same with the sample data.
Dear Josh,
Kindly find attached document for proper average calculation.
Please have a look into given below article for average,
Kind regards,
Ishfaque Ahmed
Thank you Ishfaque
The expression AVG(AGGR(XXXX,YYYY)) did the trick without the need to change the load script and breaking it into 2 separate tables.