Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 #.
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