topic Re: filtering current FYTD numbers in App Development

filtering current FYTD numbers

sharasridhar — Thu, 13 Feb 2020 18:17:39 GMT

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)

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

Re: filtering current FYTD numbers

dplr-rn — Thu, 13 Feb 2020 18:48:45 GMT

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

Re: filtering current FYTD numbers

sharasridhar — Thu, 13 Feb 2020 19:19:21 GMT

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.

Re: filtering current FYTD numbers

dplr-rn — Thu, 13 Feb 2020 19:44:51 GMT

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

Re: filtering current FYTD numbers

sharasridhar — Thu, 13 Feb 2020 19:47:32 GMT

Okay. Thank you!!

Re: filtering current FYTD numbers

dplr-rn — Thu, 13 Feb 2020 19:48:48 GMT

no problem mark as solution if it helped