Grouping Data by Month based on Base Date

[proctors]

Hi.

We have data that is collected on a per patient level at varying time points from the Baseline date (CGI_Rank in file 1 = baseline date). What I'd like to do is "normalize" the data such that no matter what the Baseline date is, each future data point is grouped based on a certain time period (e.g., Months) from the Baseline date.

Thus, if Patient A has a data point on 8/1/18, a second point on 9/5/18, and a third on 10/29/18, the base date of 8/1/18 is used as the initial date, and each point is not the exact month, but instead the number of months from the base date. Thus, second point is saved on M+2, then M+3 and so on. If there are two data points within the same month, the data should be averaged, if there is no data, then it is null.

[chrismarlow]

Hi,

Not clear if you want to try to script something here or do in a visualisation.

If you want to script a possible step 1 (if I've understood you right) would be to join back the date to itself where CGI_RANK=1;

data:
LOAD ID, 
     Date, 
     CGI_RANK, 
     IMPROVEMENT, 
     SEVERITY
FROM
[SAMPLE DATA FOR QV Group.xls]
(biff, embedded labels, table is Sheet1$);

left join (data)
Load
	ID,
	Date AS CGI_RANK1_Date
Resident data
where CGI_RANK=1;

Which gives the following;

Step 2 would involve doing a load processing the two dates (Date & CGI_RANK1_Date) … but does the above look like a step to what you want to do?

Cheers,

Chris.

[proctors]

The columns should be as follows in a Table Chart for just the Severity Scores.

ID	Date	CGI_RANK	Severity	30_Day_Grouping
51821	11/10/2016	1	4	0
51821	2/14/2017	2	4	4
51821	8/14/2017	3	3	10
51821	7/5/2018	4	4	21

The basedate (CGI_RANK = 1) would be the indicator for benchmarking the start of the period and the subsequent 30 day increments.

the 30 day values are essentially showing how many 30 day intervals that current Date is from the basedate.

[proctors · Accepted solution]

I have a colleague who was able to do it in R. No need to solve in QV.