Trying to write an experssion for a reassessment rate

[mjirges]

I am trying to write an expression for a reassessment rate. I have an assessment/test that is filled out multiple times during a patients stay, and we need to make sure they are getting the reassessment done on time. The reassessment has a window of 150-180 days past the initial "Baseline" assessment, so the data looks like this:

RecordID	Client_ID	Client_Name	Test_Date	Test_Type
1	5551212	Barb R Ann	1/1/2023	Baseline
2	5551212	Barb R Ann	5/31/2023	Reassessment
3	8675309	Jenny	1/15/2023	Baseline
4	8675309	Jenny	6/1/2023	Reassessment
5	555	Leonardo	2/1/2023	Baseline
6	555	Leonardo	7/25/2023	Reassessment
7	1212	Ralph	2/5/2023	Baseline
8	1212	Ralph	8/23/2023	Reassessment
9	867	Donatello	2/6/2023	Baseline
10	867	Donatello	8/1/2023	Reassessment
11	5309	Michelangelo	2/8/2023	Baseline
12	5309	Michelangelo	9/20/2023	Reassessment
13	55	Splints	2/11/2023	Baseline
14	55	Splints	8/1/2023	Reassessment

In this example, Barb R Ann, Leonardo, Donatello and Splits fall into the window. Jenny's was done too soon, and Ralph and Michelangelo were too late. The Reassessment rate should be 4/7 or 57%.

My data set is significantly larger, so I can't realistically do this by hand, but I'm not sure how to write it out either.

[Saravanan_Desingh]

Try this:

tab1:
LOAD * INLINE [
    RecordID, Client_ID, Client_Name, Test_Date, Test_Type
    1, 5551212, Barb R Ann, 1/1/2023, Baseline
    2, 5551212, Barb R Ann, 5/31/2023, Reassessment
    3, 8675309, Jenny, 1/15/2023, Baseline
    4, 8675309, Jenny, 6/1/2023, Reassessment
    5, 555, Leonardo, 2/1/2023, Baseline
    6, 555, Leonardo, 7/25/2023, Reassessment
    7, 1212, Ralph, 2/5/2023, Baseline
    8, 1212, Ralph, 8/23/2023, Reassessment
    9, 867, Donatello, 2/6/2023, Baseline
    10, 867, Donatello, 8/1/2023, Reassessment
    11, 5309, Michelangelo, 2/8/2023, Baseline
    12, 5309, Michelangelo, 9/20/2023, Reassessment
    13, 55, Splints, 2/11/2023, Baseline
    14, 55, Splints, 8/1/2023, Reassessment
];

Left Join(tab1)
LOAD Client_ID, Test_Date As R_Date
Resident tab1
Where Test_Type='Reassessment';

Left Join(tab1)
LOAD Client_ID, Test_Date As B_Date
Resident tab1
Where Test_Type='Baseline';

Left Join(tab1)
LOAD Client_ID, R_Date - B_Date As RR, 
	If((R_Date - B_Date)>180, 'Past',
		If((R_Date - B_Date)<150, 'Soon',
			'Perfect'
		)
	)	As RR_Status	
Resident tab1;

Expression: 

=Num(Count(DISTINCT {<RR_Status={'Perfect'}>} Client_ID)
/
Count(DISTINCT Client_ID)
,'0%')

[mjirges]

Unfortunately, I don't have access to use inline loads, or load any new data into tables. I only have the existing fields to work with, and the data set is growing by the day.

[mjirges]

Just thought I'd give an update on this one; here is what a peer of mine came up with:

Count(if(aggr(only({<[Test Type]={Reassessment}>} [Test Date])-only({<[Test Type]={’Baseline’}>} [Test Date]), [Client ID])>149 AND

aggr(only({<[Test Type]={Reassessment}>} [Test Date])-only({<[Test Type]={’Baseline’}>} [Test Date]), [Client ID])<181, [Client ID])

/

Count(distinct [Client ID])