5 Replies Latest reply: Jun 21, 2018 6:38 AM by Mahitha M RSS

    Flag creation help

    Mahitha M

      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.

        • Re: Flag creation help
          Jaume Criballés Faja



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


          • Re: Flag creation help
            omar bensalem


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

              • Re: Flag creation help
                Mahitha M

                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'

              • Re: Flag creation help
                rajesh Gurram

                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