Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mahitham
Contributor

Flag creation help

Hi Experts,

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.

1 Solution

Accepted Solutions
jaumecf23
Contributor III

Re: Flag creation help

Hi,

This is working for me:

Test:

Load*,

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 ,              -                                     

];

5 Replies
jaumecf23
Contributor III

Re: Flag creation help

Hi,

This is working for me:

Test:

Load*,

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 ,              -                                     

];

OmarBenSalem
Esteemed Contributor

Re: Flag creation help


Try:

if(len(trim("Training Completion date"))=0,'Outstanding',if("Training Completion date"-HireDate )<=30,'Completed','Not Completed'))


rajeshqvd
Contributor

Re: Flag creation help

Make sure date fields must be date format.

If(len(Training Completion date),'Completed',

    If((Today()-"HireDate")>30,'Not Completed',

    If((Today()-"HireDate")<30,'Outstanding'))) as Flag

rajeshqvd
Contributor

Re: Flag creation help

small correction

If(len(TDATE)>4,'Completed',

    If((Today()-"Date")>30,'Not Completed',

    If((Today()-"Date")<30,'Outstanding'))) as Flag

mahitham
Contributor

Re: Flag creation help

Hi Omar,

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'

Community Browser