6 Replies Latest reply on Sep 7, 2018 5:07 AM by chandrakanth k

    Find the first available date

    chandrakanth k

      Hello All,

       

      I have net due date and 6 pay cycle date.

       

      For every net due date I need to find first available date from pay cycle dates.

       

      For example for Net due date 7/18/2018 I need to get the first available calendar date from "date 3".

      The right value would 1st October 2018.

       

      How do I code this logic in tableau.

       

      Regards,

      Chandrakanth.K

        • 1. Re: Find the first available date
          Naveen B

          Hi Chandrakanth,

           

          It seems for your net due date there only one date3 is tagged

           

           

          Do you need minimum date per Net due date or Overall Minimum date then it would February 26 2018

           

          Didnt get your questions correctly

           

          Could you please provide some illustration with an example O/P how you need it

           

          BR,

          NB

          • 2. Re: Find the first available date
            Tushar  More

            Hi Chandrakanth,

             

            Hope I have understood your question correctly.

             

            Please check the attached.

             

            ~Tushar

            If this helps please mark the answer as correct and helpful.

            • 3. Re: Find the first available date
              chandrakanth k

              Hello Naveen,

               

              For July 18 2018 the next available date as per calendar is 1st October 2018.1st October 2018 is in "date 3" column.

               

              Lets say I have invoice due on July 18 2018 and it missed the previous pay cycle as per "date 3" Column and but now its ready to pay but my next pay cycle is on 1st October 2018. So I need to look at each net due date and get the next available date  from "date 3" field.

               

              In date 3 field I have date for 2018 and 2019.

               

              The closest date for July 2018 as per date 3 field is july 30, jul 2, aug 27 and 1st October.

              Since now we have passed july 30, jul 2, aug 27, the next available calendar date is 1st October 2018.

               

              So I need to get next available current calendar date from Date "3" for each net due date.

               

               

              Regards,

              Chandrakanth.K

              • 4. Re: Find the first available date
                chandrakanth k

                Hello Tushar,

                 

                For July 18 2018 the next available date as per calendar is 1st October 2018.1st October 2018 is in "date 3" column.

                 

                Lets say I have invoice due on July 18 2018 and it missed the previous pay cycle as per "date 3" Column and but now its ready to pay but my next pay cycle is on 1st October 2018. So I need to look at each net due date and get the next available date  from "date 3" field.

                 

                In date 3 field I have date for 2018 and 2019.

                 

                The closest date for July 2018 as per date 3 field is july 30, jul 2, aug 27 and 1st October.

                Since now we have passed july 30, jul 2, aug 27, the next available calendar date is 1st October 2018.

                 

                So I need to get next available current calendar date from Date "3" for each net due date.

                 

                 

                Regards,

                Chandrakanth.K

                • 5. Re: Find the first available date
                  chandrakanth k

                  Hello Naveen,

                   

                  In excel I am able to achieve this by using Index and match functions.

                   

                  Due date is in sheet 1 N2 column and date 3 is in E column in sheet 2

                   

                  INDEX('L2 Payment Calendar'!E:E,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1))

                   

                  Regards,

                  Chandrakanth.K

                  • 6. Re: Find the first available date
                    chandrakanth k

                    Hi Tushar,

                     

                    Hello Naveen,

                     

                    In excel I am able to achieve this by using Index and match functions.

                     

                    Due date is in sheet 1 N2 column and date 3 is in E column in sheet 2

                     

                    INDEX('L2 Payment Calendar'!E:E,MATCH((IF(N2<TODAY(),TODAY()+1,N2+1))

                     

                    Regards,

                    Chandrakanth.K