topic Re: Calculated Dimension in QlikView

Calculated Dimension

Thu, 14 Jan 2016 13:23:12 GMT

I am struggling with a calculated dimension.

I am trying to transform the below:

Name	Type	Score
Mike	1	2
Mike	2	2
Mike	2	2
Mike	3	3
Paul	1	2
Paul	1	1
Paul	2	2
Paul	2	2
Paul	2	2
Steve	1	1
Steve	2	1
Steve	3	2
Steve	3	3
Harvey	1	4
Harvey	2	2
Harvey	3	2
Edward	1	3
Edward	2	2

Into this:

Name	Avg Score
Paul	1.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).

Name	Avg Score
Mike	2
Paul	1.8
Steve	1.33
Harvey	3
Edward	2.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)

Re: Calculated Dimension

sunny_talwar — Thu, 14 Jan 2016 19:38:59 GMT

Try this as your calculated dimension:

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

Re: Calculated Dimension

MarcoWedel — Fri, 15 Jan 2016 01:34:28 GMT

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

Re: Calculated Dimension

jagan — Fri, 15 Jan 2016 01:53:48 GMT

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.