Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
proctors
Creator
Creator

Grouping Data by Month based on Base Date

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.

 

Labels (1)
  • group

1 Solution

Accepted Solutions
proctors
Creator
Creator
Author

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

View solution in original post

3 Replies
chrismarlow
Specialist II
Specialist II

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;

20190313_1.png

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
Creator
Creator
Author

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

IDDateCGI_RANKSeverity30_Day_Grouping
5182111/10/2016140
518212/14/2017244
518218/14/20173310
518217/5/20184421


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
Creator
Creator
Author

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