7 Replies Latest reply on Sep 7, 2018 2:45 PM by Shinichiro Murakami

    Is there a way to replicate excel index & match function in tableau?

    chandrakanth k

      Hello All,

       

      In my data source I have two excel work books

       

      1. LDB V3 and 2. Calendar. I blended both files in tableau using System as key column.

       

      In LDB V3 I have due date and based on due date I need to find next available date in calendar and call it pay date.

       

      I was able to do this in excel using Index function. How do I replicate this in tableau?

       

      For line one where net due date is Jul 25 2018, I need to get first available date from "Calendar" which is 7/30/2018.

      If system is SAP and payment vendor is D and payment schedule is Rglr I need to refer to column "Biweekly - D" in calendar to arrive pay date 7/30/2018.

      In my secondary source I have 5 columns with different pay cycle dates

      Regards,

      Chandrakanth.K

        • 1. Re: Is there a way to replicate excel index & match function in tableau?
          Jim Dehner

          good morning

          this will return the next date

           

          in your text chart set it up like this

          and it will return

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 3. Re: Is there a way to replicate excel index & match function in tableau?
            chandrakanth k

            Hello Jim,

             

            The above didn't give the expected result. I am rephrasing the question and also re-organized data and updated to new attached workbook.

             

            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 which is available in "date 3" Dimension

             

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

             

            • 4. Re: Is there a way to replicate excel index & match function in tableau?
              Jim Dehner

              Hi see the attached

               

              this gets messy - it is equivalent to a vlookup set to TRUE in excel

               

              the calculation checks to see if the "Net due date" is between 2 dates and then returns the greater

               

               

               

              if Netduedate <= date('11/25/2019') and Netduedate > date('10/28/2019') then date('11/25/2019')

              elseif Netduedate <= date('10/28/2019') and Netduedate > date('9/30/2019') then date('10/28/2019')

              elseif Netduedate <= date('9/30/2019') and Netduedate > date('9/2/2019') then date('9/30/2019')

              elseif Netduedate <= date('9/2/2019') and Netduedate > date('7/29/2019') then date('9/2/2019')

              elseif Netduedate <= date('7/29/2019') and Netduedate > date('7/1/2019') then date('7/29/2019')

              elseif Netduedate <= date('7/1/2019') and Netduedate > date('6/27/2019') then date('7/1/2019')

              elseif Netduedate <= date('6/27/2019') and Netduedate > date('5/29/2019') then date('6/27/2019')

              elseif Netduedate <= date('5/29/2019') and Netduedate > date('4/1/2019') then date('5/29/2019')

              elseif Netduedate <= date('4/1/2019') and Netduedate > date('2/25/2019') then date('4/1/2019')

              elseif Netduedate <= date('2/25/2019') and Netduedate > date('1/28/2019') then date('2/25/2019')

              elseif Netduedate <= date('1/28/2019') and Netduedate > date('1/1/2019') then date('1/28/2019')

              elseif Netduedate <= date('1/1/2019') and Netduedate > date('11/26/2018') then date('1/1/2019')

              elseif Netduedate <= date('11/26/2018') and Netduedate > date('10/29/2018') then date('11/26/2018')

              elseif Netduedate <= date('10/29/2018') and Netduedate > date('10/1/2018') then date('10/29/2018')

              elseif Netduedate <= date('10/1/2018') and Netduedate > date('8/27/2018') then date('10/1/2018')

              elseif Netduedate <= date('8/27/2018') and Netduedate > date('7/30/2018') then date('8/27/2018')

              elseif Netduedate <= date('7/30/2018') and Netduedate > date('7/2/2018') then date('7/30/2018')

              elseif Netduedate <= date('7/2/2018') and Netduedate > date('5/28/2018') then date('7/2/2018')

              elseif Netduedate <= date('5/28/2018') and Netduedate > date('4/30/2018') then date('5/28/2018')

              elseif Netduedate <= date('4/30/2018') and Netduedate > date('4/2/2018') then date('4/30/2018')

              end

               

              it returns this

               

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 5. Re: Is there a way to replicate excel index & match function in tableau?
                chandrakanth k

                Hello Jim,

                 

                I made some changes to the calculated field that you have shared and its worked. Thank you for your support.

                 

                Regards,

                Chandrakanth.K

                • 7. Re: Is there a way to replicate excel index & match function in tableau?
                  Shinichiro Murakami

                  Hi chandrakanth k  Jim Dehner

                   

                  Sorry for jumping in after discussion finished.

                  You can use other typical solution after 10.5 release. ==> Range Join in some case.

                   

                   

                   

                   

                  Pick Minimum date

                   

                   

                   

                   

                  Thanks,

                  Shin