6 Replies Latest reply on Mar 22, 2017 8:48 AM by Jilani Shaik

    Need Logic for Date functions

    Jilani Shaik


      in the above img


      i need to calculate Gap Period in between the start date and end date


      cust id is repeated in multiple regions i want to calculate diff in between start date and end date othere wise no need to calculate


      suppose region is west cust id is 100 then get max(start)=1/20/2017

                     region is east cust id is 100 then get max(end)=2/10/2017

      so the differnce for west region is datediff('day',1/20/2017,2/10/2017)

                                        north region is datediff('day',2/1/2017,2/20/2017)


      for east region no need to calculate


      for each cust id


      when region =west then max(start) // max(end)

               region=north then max(start) // max(end)

               region=east then max(end)


      if region =east then 0

      elseif region=west and max(east end date)<max(west start date) then datediff('day',max( west start date),max(east end date))

      else 0

      elseif region=north and max(west end date)<max(north start date) then datediff('day',max(north start date),max(west end date))


      like the following regions


      any body plz help me asap

        • 1. Re: Need Logic for Date functions
          Tom W


          If you need help ASAP please start by uploading a Tableau Packaged Workbook including sample data so we can replicate.

          1 of 1 people found this helpful
          • 2. Re: Need Logic for Date functions
            Walt Reed

            Hi Jilani,

            Just so I understand the logic, you want the start date to be the MAX for that particular Region, but the end date should be the MAX for the previous Region?



            • 3. Re: Need Logic for Date functions
              Jilani Shaik

              Hi Walt



              for west i need to calculate max(west start date) - max(east end date)

              north i need to calculate max(north start date) - max(west end date)


              Plz help me asap

              • 4. Re: Need Logic for Date functions

                Hi Jilani,


                Have you tried using FIXED LOD ?


                1) WEST START DATE (Calculate the MAX Start date of WEST)



                {FIXED [Cust ID],[Region]:MAX(START DATE)}



                2) EAST END DATE (Calculate the MAX Start date of each region)



                {FIXED [Cust ID],[Region]:MAX(END DATE)}


                3) DATE DIFF (Calculate the Date difference)

                DATEDIFF('day',[WEST START DATE],[EAST END DATE])

                • 5. Re: Need Logic for Date functions
                  Walt Reed

                  Hi Jilani,

                  See attached workbook. Your problem is complicated by the fact that you need the previous Region's max end date. I was able to get somewhat of a workaround to get your desired result. In essence, I created a few calculated fields:

                  NEW START (MAX start date of each Region - excluding East Region : { FIXED [Cust Id],[Region]:MAX(IF [Region] <> 'East' THEN [Start Date] END)}

                  NEW END1 (MAX end date of each Region): { FIXED [Cust Id],[Region]:MAX([End Date])}

                  NEW END2 (perform LOOKUP function on NEW END1 to get values to move down 1 row): LOOKUP(MAX([New End]),-1)

                  DATEDIFF (perform DATEDIFF function, but set values to NULL if NEW END2 did not move down 1 row):

                  IF ISNULL(DATEDIFF('day',MIN([NEW END]),[NEW END2]))

                  OR DATEDIFF('day',MIN([NEW END]),[NEW END2]) = 0 THEN NULL

                  ELSE DATEDIFF('day',MIN([NEW START]),[NEW END2])




                  Hopefully this gives you what you were looking for.



                  • 6. Re: Need Logic for Date functions
                    Jilani Shaik

                    Hi walt

                    Thanks for your valueable reply.


                    but east is repeated 4 times with different order id and common cust id

                    and as well as south and north.



                    in that case lookup doesnt give the correct result.