Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

nboccassini
Contributor

Average problem

Hi,

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

This is the data table

SKILLEMPLOYAB
SKILL1EMPL152
SKILL1EMPL263
SKILL2EMPL141
SKILL2EMPL285
SKILL3EMPL132
SKILL3EMPL241

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.

7 Replies
MVP
MVP

Re: Average problem

Create a Straight Table

Dimension

SKILL

EMPLOY

Expression

SUM(A)/SUM(B)

or

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))

nboccassini
Contributor

Re: Average problem

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

MVP
MVP

Re: Average problem

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

perumal_41
Valued Contributor II

Re: Average problem

Hi

Try below expression .

 

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

nboccassini
Contributor

Re: Re: Average problem

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

MVP
MVP

Re: Average problem

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

Re: Re: Average problem

Hi,

another one:

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

QlikCommunity_Message_140377_Pic1.JPG.jpg

hope this helps

regards

Marco