Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
rashmithakur
Contributor II
Contributor II

Calculate scheduled date and check if a dose date is entered in each week for each ID

Hi, 

I would need support on the below scenario

ID      enroll_date        Dose_date
001  29-Jan-2021       2/6/2021
001  29-Jan-2021       2/13/2021
001  29-Jan-2021       2/13/2021
001  29-Jan-2021       2/28/2021
001  29-Jan-2021         
002  02-Feb-2021        2/9/2021
002  02-Feb-2021         2/17/2021
002  02-Feb-2021         2/18/2021
002  02-Feb-2021        3/1/2021

Req:
Calc scheduled date based on enroll_date for each ID which will be :
logic to calc scheduled date Scheduled Date

ID    enroll_date     logic                   scheduled date
001  29-Jan-2021  enroll_date+7    2/5/2021
001  29-Jan-2021  enroll_date+14  2/12/2021
001  29-Jan-2021  enroll_date+21   2/19/2021
001  29-Jan-2021                                    2/26/2021
001  29-Jan-2021                                    3/5/2021
002  02-Feb-2021  enroll_date+7      2/9/2021
002  02-Feb-2021  enroll_date+14    2/16/2021
002  02-Feb-2021  enroll_date+21    2/23/2021
002  02-Feb-2021                                    3/2/2021

enrol date is same for each ID. I need to calculate Scheduled dates for each ID which should be the increment of 7 from the previous date and loop it for 20 times.

Once scheduled date is calculated, I have to check for each ID, if a dose is taken in each week (in calc Scheduled Date column)
If a dosed is missed is any of the week, that needs to be flagged.

Thanks

2 Replies
Dalton_Ruer
Support
Support

You will need to build out a Master Calendar. (Search for that along with Qlik to find many examples)

You will want a calendar that has every single day along with some flags that say what week it is in. Qlik has a simple Week function. 

Meaning if you consider this week let's say it is the 17'th week of the year. So Monday - Friday are all part of Week 17. If patient is dosed anytime this week, they are do again sometime in Week #18. 

Then you will want to use the AutoGenerate function (see this post https://community.qlik.com/t5/Qlik-Design-Blog/Autogenerate-Your-Data/ba-p/1465273) So that you can construct a table for each patient that has X number of targeted rows that increment from their starting week #.

 

rashmithakur
Contributor II
Contributor II
Author

Dear Dalton,

Thanks for  providing  with the great explanation on the approach to go ahead on the solution. I am very new to QlikSense, and I am already working the script  with the approach suggested. But can someone help me by providing any reference to a script which has worked on the similar approach. This will help me a lot. Thanks