Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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
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))
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:
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!