Flag creation help

[mahitham]

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.

[jaumecf23 · Accepted solution]

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]

Try:

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

[rajeshqvd]

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]

small correction

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

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

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

[mahitham]

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'