topic Re: Qlik Sense app- count with multiple criteria in App Development

Qlik Sense app- count with multiple criteria

aroyi — Sat, 16 Nov 2024 21:37:34 GMT

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:

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”
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

Re: Qlik Sense app- count with multiple criteria

jonathandienst — Thu, 10 Jan 2019 06:44:08 GMT

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))

Re: Qlik Sense app- count with multiple criteria

aroyi — Fri, 11 Jan 2019 01:26:48 GMT

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:

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”.
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.
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!