Set analysis help

[jduenyas]

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

[jagan]

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.

[qlikviewwizard]

I have done the same with the sample data.

[engishfaque · Accepted solution]

Dear Josh,

Kindly find attached document for proper average calculation.

Please have a look into given below article for average,

Average – Which average?

Kind regards,

Ishfaque Ahmed

[jduenyas]

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.