topic Set analysis help in QlikView

Set analysis help

jduenyas — Thu, 10 Sep 2015 00:20:24 GMT

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

Re: Set analysis help

jagan — Thu, 10 Sep 2015 01:30:22 GMT

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)

=Avg(Score_A)

Hope this helps you.

Regards,

Jagan.

Re: Set analysis help

qlikviewwizard — Thu, 10 Sep 2015 01:40:13 GMT

I have done the same with the sample data.

Re: Set analysis help

engishfaque — Thu, 10 Sep 2015 04:34:39 GMT

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

Re: Set analysis help

jduenyas — Thu, 10 Sep 2015 15:29:20 GMT

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.