Qlik Sense app- count with multiple criteria

[aroyi]

Hi,

I have  data with the following fields:

episode_key: unique numbers,

measure_date: dates,

reason(coded): 3 categories- 1, 2, 3

score: integer

 Sample of data:

episode_key	measure_date	reason	score
0001	1/12/2017	1	23
0001	3/09/2017	3	21
0002	2/02/2018	1	45
0002	1/01/2017	2	34
0003	23/11/2017	1	36

I need to build a Qlik Sense app(KPI) with rules that count  unique number of episode_keys that meet the following criteria:

1. For each episode_key, there may be 0 to multiple entries of scores with different codes and dates. Count only episode_key  that have "reason"  both “1”&”3”
2. For each episode_key, "measure_date" for reason “3” have to be after the "measure_date" of reason “1”, not before or on the same date.

I'm new to using syntax in Qlik Sense. I've read some previous posts on count with multiple conditions in Qlik View. But I'm not sure if this can be done in Qlik Sense? Can someone please help me? Thanks a lot!

Kind Regards,

Nina

[jonathandienst]

Perhaps

Count(DISTINCT Aggr(
	If(Count({<reason = {1,3}>} episode_key) = 2
		And Only({<reason = {3}>} measure_date) > Only({<reason = {1}>} measure_date), 
	episode_key)
, episode_key))

[aroyi]

Hi Jontydkpi,

Thanks for your help. I ran your code, it didn't show any errors but the result is 0 which is incorrect. Not sure what went wrong?

And I don’t quite follow the last two lines:

episode_key)

, episode_key))

Should the syntax end like this: }>} episode_key) ?

Please ignore the rule No.3 as it's a repeat of rule No 2. So to clarify, here are the criteria to count episode_key:

1. For each episode_key, there may be 0 to multiple entries of scores with different dates. Count only episode_key that have "reason"  both “1”&”3”.
2. For each episode_key, "measure_date" for reason “3” have to be after the "measure_date" of reason “1”, not before or on the same date.
3. Exclude "episode_key" with “score” value of “0” from the count.

Here’s my updated version based on your code:

Count(DISTINCT Aggr(

            If(Count({<reason = {1,3}>} episode_key) = 2

                        And Only({<reason = {3}>} measure_date) > Only({<reason = {1}>} measure_date), <score<>{“0”}>,

            episode_key)

, episode_key))

No syntax errors but the result is still 0. What’s wrong? Can someone help? Thanks a lot!