topic Re: Conversion to Set Analysis in QlikView

Conversion to Set Analysis

kevbrown — Thu, 04 Aug 2016 08:18:38 GMT

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 — Thu, 04 Aug 2016 08:23:25 GMT

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 — Thu, 04 Aug 2016 08:30:17 GMT

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 — Thu, 04 Aug 2016 09:17:51 GMT

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 — Thu, 04 Aug 2016 10:00:18 GMT

Worked a treat, thanks