Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
aroyi
Contributor III
Contributor III

Qlik Sense app- count with multiple criteria

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_keymeasure_datereasonscore
00011/12/2017123
00013/09/2017321
00022/02/2018145
00021/01/2017234
000323/11/2017136

 

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

Labels (1)
2 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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))
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
aroyi
Contributor III
Contributor III
Author

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!