Discussion board where members can get started with Qlik Sense.
Can any one please help me on below requirement.
I have to create a flag with completed,Not Completed and Outstanding records. Some training completion dates contains null like below.
Employee HireDate Training Completion date Flag
ABC 12/03/2018 - Not Completed
DEF 14/05/2018 18/05/2018 Completed
GHI 04/06/2018 - Outstanding
completed --> When user completes the training within 30days from the Hire date
Not Completed--> When user Not completed the training within 30days from the Hire date
Outstanding --> When user has still time to complete from hire date out of 30days.
I have tried like below but getting Not completed for 04/06/2018. This employee has the deadline up to 04/07/2018 by adding 30days .Please help me to show outstanding.
if((Training Completion date-HireDate )<=30,Completed,Not Completed)
Thanks in advance.
Go to Solution.
This is working for me:
if([Training Completion date]-HireDate<=30,'Completed',
if(Today()-HireDate<=30,'Outstanding','Not Completed')) as Status
LOAD * INLINE [
HireDate , Training Completion date
12/03/2018 , -
14/05/2018 , 18/05/2018
04/06/2018 , -
if(len(trim("Training Completion date"))=0,'Outstanding',if("Training Completion date"-HireDate )<=30,'Completed','Not Completed'))
Make sure date fields must be date format.
If(len(Training Completion date),'Completed',
If((Today()-"HireDate")<30,'Outstanding'))) as Flag
If((Today()-"Date")<30,'Outstanding'))) as Flag
Thanks for your reply.
By taking len(trim("Training Completion date"))=0 for 12/03/2018 also getting 'Outstanding' its more than 30days from hire date so need to show not completed.
04/06/2018 is not more than 30 days from hire date so need to show 'Outstanding'