AND/OR condition in set analysis

[stascher]

Hello,

Given the following data...

What would the set analysis be to count all of the Subjects who are: (Enrolled or Stoned) and Failing? 

I found the following works: count({<EnrolledYN={Y},FailingYN={Y}>+<StonedYN={Y},FailingYN={Y}>}Subject)

but is there a more efficient way to write it without having to specify FailingYN multiple times? 

Thanks.
Steve

[marcus_sommer · Accepted solution]

Depending on your real dataset you may try: 

{<FailingYN={Y}>} count({<EnrolledYN={Y}>+<StonedYN={Y}>}Subject)

Beside this you may consider to make the appropriate checks already within the data-model because these flags might be not just Y/N else 1/0 or a dual() of it as well as something like pow(2, n) and the summed within another flag. By only 3 flag-fields this may too expensive but if there are a lot more and various kinds of queries it may save a lot of efforts. 

[Chanty4u]

Check this 

COUNT({<FailingYN={'Y'}, EnrolledYN={'Y'}+StonedYN={'Y'}>} Subject)

[stascher]

Thanks Marcus.

I've never seen set analysis used outside of a function, but it works. Is this use case documented in Qlik Sense help anywhere? Can I ask what makes this approach "expensive" vs putting set analysis inside a function? 

Regards,

Steven

[stascher]

Unfortunately this approach caused an expression error. Thanks for trying though.

[marcus_sommer]

It's documented here: Set analysis | Qlik Cloud Help and more background (see also the various comments) is here: New Set Analysis syntax - Page 8 - Qlik Community - 1983110. Be aware that this powerful extending of the set analysis is increasing the complexity and not the opposite - and inner and outer statements aren't blindly replaceable or mixable.

The hint in regard to the expensive efforts of a possible flag-handling means that flags could be also categorized and grouped especially if they have a numerical value because then the sum/max/min/avg of them could summarize the meaning and/or with flags which follow an exponential logic it's possible to combine n flags within a single one. For only 3 flags it's too complex but if there are several dozen of flags in scoring-scenarios or similar stuff you won't be happy to address and query them all within nested logic.