Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

filtering current FYTD numbers

I have a data set with four fiscal years. I need two KPIs, one with new patients and the other with returning patients. 

I began with creating a fiscal calendar with flags for currentFY. To get new patients I used the count(distinct(PatientID)) which gave me the number instances new patients came into the hospital system through all the four years. 

Now I need to filter out the new patients for the currentFY. 

I am using: 

count({$<currentFYFlag={'1'}>}distinct(PatientID))

I am getting a number. But I am not sure if the set analysis is

first filtering the data to the current year and then finding the count of distinct patientIDs (i don't want this) 

or

first getting the distinct patientIDs and then filtering to the current year (this is what I need).

5 Replies
Highlighted
Partner
Partner

set analysis is first filtering the data to the current year .

I dont see how numbers would be different. maybe you can elaborate with sample data and example

Highlighted
Contributor III
Contributor III

If the formula filters the unique IDs first and then by year then it would give the true unique IDs within the whole dataset. 

If it filters the year and then by unique IDs; this would give unique IDs only in that year. But the patient with that ID could have come the previous year. In such a case its a new entry only to that year not new to the system and I need to count the new patients entering to the system.

Highlighted
Partner
Partner

Ok. unfortunately set analysis doesn't work that way.

there maybe a round about way to do this with expression but i feel easiest and best in terms of performance maybe to mark each new patient with a flag.

e.g. rough code below

PatientDetailsTable:

load

       patientid,

      admissiondate,

      dummycolumn

from source;

left join (PatientDetailsTable)

load min(admissiondate) as admissiondate , patientid,  1 as firstadmissionflag

from source groupby patientid

this way each first admitted row will be marked with firstadmissionflag as 1.

use that in your set analysis along with currentfyflag

 

 

 

Highlighted
Contributor III
Contributor III

Okay. Thank you!!

Highlighted
Partner
Partner

no problem mark as solution if it helped