3 Replies Latest reply on Nov 3, 2016 9:40 AM by MichaelH H

    Please help to solve complicate date calculation

    MichaelH H

      Dear Tableau Community,

       

      Please help on the calculations below, I'm able to calculate the below in excel but unable to do in Tableau, I would really appreciate your help guys.

      I attached the excel file.

       

      if Count Date is blank and Pending = 4 then Today's Date + Days Interval

      but if Count Date > 1 or Pending = 3 or Pending = 2 or Pending = 1 and also if Count Date > 1 and Pending = 3 then Today's Date + Days Interval

      but if Count Date > 1 or Pending = 2 or Pending = 1 and also if Count Date > 1 and Pending = 2 then Today's Date + Days Interval

      but if Count Date > 1 or Pending = 1 or Pending = 0 and also if Count Date > 1 and Pending = 1 then Today's Date + Days Interval

      and lastly if Count Date > 1 or Pending = 0 then the field will be blank or no date

       

      Thanks again,

      Michael

        • 1. Re: Please help to solve complicate date calculation
          Shinichiro Murakami

          I don't know Tableau new version has NETWORKING DAYS or not, but I created alternative one anyway.

           

           

          [End Date test] and [Today Date test] are used for the purpose of weekend test.  You can replace those to [End Date] and today() respectively

          [Count date adj]

          [Count Date]

          -(if datename('weekday',[Count Date])="Saturday" then 1 else 0 end )

          -(if datename('weekday',[Count Date])="Sunday" then 2 else 0 end )

          [End Date adj]

          [End Date test]

          -(if datename('weekday',[End Date test])="Saturday" then 0 else 0 end )

          -(if datename('weekday',[End Date test])="Sunday" then 1 else 0 end )

          [Todays Date adj]

          [Today Date test]

          -(if datename('weekday',[Today Date test])="Saturday" then 1 else 0 end )

          -(if datename('weekday',[Today Date test])="Sunday" then 2 else 0 end )

           

          [Days Left to Count]

          ([End Date adj]-[Todays Date adj])-floor(([End Date adj]-[Todays Date adj])/7)*2

          [End Date - Count Date]

          ([End Date adj]-[Count date adj])-floor(([End Date adj]-[Count date adj])/7)*2

          Count Date - Today 's description and formula did not match, then I considered formula as correct.

           

          [Days Interval]

          floor([Days Left to Count]/[Pending])

           

          [Required Value]

          if [Pending 2] <> 0 then [Today Date]+[Days Interval] end

          [Pending 2]  is for the purpose of the test. You can replace this to [Pending]

           

           

          Looking at your conditions. only the differnece is when Pending = 0. so I simplified the formula.

          • if Count Date is blank and Pending = 4 then Today's Date + Days Interval
          • but if Count Date > 1 or Pending = 3 or Pending = 2 or Pending = 1 and also if Count Date > 1 and Pending = 3 then Today's Date + Days Interval
          • but if Count Date > 1 or Pending = 2 or Pending = 1 and also if Count Date > 1 and Pending = 2 then Today's Date + Days Interval
          • but if Count Date > 1 or Pending = 1 or Pending = 0 and also if Count Date > 1 and Pending = 1 then Today's Date + Days Interval
          • and lastly if Count Date > 1 or Pending = 0 then the field will be blank or no date

           

           

           

           

           

          Thanks,

          Shin

          • 2. Re: Please help to solve complicate date calculation
            MichaelH H

            Dear Shin,

             

            Thank you very much for your time and effort on solving these, I will try the solutions and will get back to you.

             

            I really  appreciate your help.

             

             

            Thanks again,

            Michael

            1 of 1 people found this helpful
            • 3. Re: Please help to solve complicate date calculation
              MichaelH H

              Dear Shinichiro,

               

              I tried your calculations and it was very helpful thanks again to you. I'm happy I found someone who works in the same field (Supply Chain).I will show you the dashboard once I'm done. Please add me to your contacts.

               

              Thanks again,

              Michael