Conversion to Set Analysis

[kevbrown]

From the project I've taken on I have the following expression -

=Num(Sum(if(([CPA Formal Review.Age_18_65]) = 'Y' and ([CPA Formal Review.On CPA over 1 Year]) = 'Y' and

([CPA Formal Review.Seen_in_12Months]) = 'Yes' and ([Team Type])<>'Wellbeing Service',1,0)) / Sum(if(([CPA Formal Review.Age_18_65]) = 'Y'

and ([CPA Formal Review.On CPA over 1 Year]) = 'Y' and ([Team Type]) <> 'Wellbeing Service'

,1,0)),'#.##%')

Surely it would be better to use Set Analysis for this but I'm not sure you can for the <>(Not like) part, can you? 

Thanks

[Mark_Little]

HI,

In set analysis you can -= for not equals.

Also i would look at adding flags in script for some of the strings. have 1 for yes and 0 for no.

Mark

[squeakie_pig · Accepted solution]

Another way to do not equals would be to have the following in your set analysis:

[Team Type] = {'*'} - {'Wellbeing Service'}

Makes it easier to debug set analysis because qlikview thinks -= is an error.

[Anil_Babu_Samineni]

Perhaps This...!

=Num(

     Sum(

          if(

               [CPA Formal Review.Age_18_65] = 'Y' and

               [CPA Formal Review.On CPA over 1 Year] = 'Y' and

               [CPA Formal Review.Seen_in_12Months] = 'Y' and

               [Team Type] <> {'Wellbeing Service'},1,0))

/

Sum(

  if(

  [CPA Formal Review.Age_18_65] = 'Y' and

  [CPA Formal Review.On CPA over 1 Year] = 'Y' and

[Team Type] <> {'Wellbeing Service'},1,0),'#.##%'))

Can you please let me know about this - What do you want to Achieve this? [Team Type] <> {'Wellbeing Service'}

[kevbrown]

Worked a treat, thanks