topic Re: Average problem in QlikView

Average problem

nboccassini — Fri, 31 Oct 2014 11:40:31 GMT

Hi,

I have a problem on an average in a text box.

This is the data table

SKILL	EMPLOY	A	B
SKILL1	EMPL1	5	2
SKILL1	EMPL2	6	3
SKILL2	EMPL1	4	1
SKILL2	EMPL2	8	5
SKILL3	EMPL1	3	2
SKILL3	EMPL2	4	1

I need to calculate the average of A/B for each SKILL and count how many averages are <0,8 and show it in a text box.

Thanks.

Re: Average problem

MK_QSL — Fri, 31 Oct 2014 11:49:00 GMT

Create a Straight Table

Dimension

SKILL

EMPLOY

Expression

SUM(A)/SUM(B)

Create a Straight Table

Dimension

SKILL

Expression

SUM(A)/SUM(B)

Create a Text Box and write below expression

=SUM(IF(Aggr(SUM(A)/SUM(B),SKILL)<0.8,1,0))

Re: Average problem

nboccassini — Fri, 31 Oct 2014 12:11:08 GMT

The expression may be correct, but the problem is I have more rows in my data model with the same value of EMPLOY, A and B so I need the expression must considers A/B only one time for each EMPLOY

Re: Average problem

MK_QSL — Fri, 31 Oct 2014 12:24:00 GMT

Provide sample data in excel along with the result your expecting...

Re: Average problem

perumal_41 — Fri, 31 Oct 2014 12:36:04 GMT

Try below expression .

Count ({<SKILL={"=SUM(A)/SUM(B)<0.8"}>}Distinct EMPLOY)

Re: Re: Average problem

nboccassini — Fri, 31 Oct 2014 13:10:31 GMT

This is a qvw example. Note that in the FACT table EMPL,A and B may be duplicated

Re: Average problem

MK_QSL — Fri, 31 Oct 2014 13:20:11 GMT

=SUM(IF(Aggr(AVG(A/B),Skill)<=0.9,1,0))

Re: Re: Average problem

MarcoWedel — Fri, 31 Oct 2014 23:25:09 GMT

Hi,

another one:

=-SUM(Aggr(AVG(A/B),Skill)<0.9)

hope this helps

regards

Marco