Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculated Dimension

Hi

I am struggling with a calculated dimension.

I am trying to transform the below:

   

NameTypeScore
Mike12
Mike22
Mike22
Mike33
Paul12
Paul11
Paul22
Paul22
Paul22
Steve11
Steve21
Steve32
Steve33
Harvey14
Harvey22
Harvey32
Edward13
Edward22

Into this:

   

NameAvg Score
Paul1.8

But I get this:

Dimension: if(Type=1 or Type=2, Name)

Expression: avg(Score)

The issue is that I only want to return Names that have not received a Type 3 (based on this, Mike, Steve and Harvey should be excluded), the second condition I want to incorporate is to only show me names that have an average score of lower than 2 (so only Paul should show in my end table).

   

NameAvg Score
Mike2
Paul1.8
Steve1.33
Harvey3
Edward2.5

I tried =avg({<score={'<2'}>}score) but this only gives me the average of any scores above 2. So I am now using if(avg=(score)<=2,avg(score)), this now working I believe but it shows Mike and Steve still.

How can I filter out these individuals based on the above criteria? (even if they have type 1 or 2, I still need to filter them out)

1 Solution

Accepted Solutions
jagan
Partner
Partner

Hi,

Try like  this using Set Analysis, Calculated Dimension will have performance issues

Dimension: Name

Expression=If(Avg({<Name = e({<Type = {3}>})>} Score) < 2, Avg({<Name = e({<Type = {3}>})>} Score))

Regards,

jagan.

View solution in original post

3 Replies
sunny_talwar

Try this as your calculated dimension:

=Aggr(Only({<Name = e({<Type = {3}>}) * {"=Avg(Score) < 2"} >} Name), Name)

MarcoWedel

Hi,

best you tried to avoid calculated dimensions in this case and used a set expression like sunny's in your expression instead.

This should result in a better performance.

hope this helps

regards

Marco

jagan
Partner
Partner

Hi,

Try like  this using Set Analysis, Calculated Dimension will have performance issues

Dimension: Name

Expression=If(Avg({<Name = e({<Type = {3}>})>} Score) < 2, Avg({<Name = e({<Type = {3}>})>} Score))

Regards,

jagan.