3 Replies Latest reply on Apr 25, 2018 7:32 AM by Zhouyi Zhang

    Delivery Spectrum

    Benjamin Rubattel

      Hello Everyone,

       

      I am trying to calculate the Period of Delivery to Know the number of business days(excluding Saturday & Sundays) our supplier delivery the goods either earlier or late.

       

      E.g on Excel: this is on Networdays, quite simple to create in Excle

       

       

      I'd like to do the same in Tableau, I looked up in the forum if there was any similare threads and found the below:

       

      https://community.tableau.com/message/288113#288113

        https://community.tableau.com/message/469623#469623 :

       

       

      I tried both but it seems to have some difficulties to calculate the number of days where the Posting Date (should be the end date) was made before the delivery Date (which should be the start date) so I reach figures as beloe:

       

       

      the two first line are correct but the last one (20) is wrong..

       

      Has one of you guys an Idea of what can be done? I attached the Data for this example.

       

      thanks

      Benjamin

        • 1. Re: Delivery Spectrum
          Zhouyi Zhang

          Hi, Benjamin

           

          To achieve similar function as excel, you need join your data with a calendar table as shown below

           

           

          And then create calculation fields to filter and count days

           

          An updated workbook attached for your reference. Hope this helps

           

          ZZ

          • 2. Re: Delivery Spectrum
            Benjamin Rubattel

            Thanks for your input.

             

            the line 20 should be a negative value as the Posting Date is before the Delivery date.

             

             

            I am trying the formula below which seems to work quite fine, however I have an issue in the case the End date is before the Start Date and between two years..

             

             

            ([Posting Date]-[Delivery Date - Statistic])-

            If

            (DATEDIFF('year',[Delivery Date - Statistic], [Posting Date]))>=1

            then

            (2*((DATEPART('week', [Posting Date]) + 52*DATEDIFF('year',[Delivery Date - Statistic], [Posting Date])) - DATEPART('week', [Delivery Date - Statistic])))

            else

            (2*(DATEPART('week', [Posting Date]) -DATEPART('week', [Delivery Date - Statistic])))

            End

            +

            1

             

             

            Any ideas of why?

             

            Benjamin

            • 3. Re: Delivery Spectrum
              Zhouyi Zhang

              Hi, Benjamin

               

              Please find my updates below

               

              Please let me know if it works or not, if not, can you please share your workbook of where you stuck?

               

              ZZ