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

    Need Logic for Date functions

    Jilani Shaik

      Hi

      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

          Hi,

          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?

             

            Walt

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

              Hi Walt

               

              yes

              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
                sonya.jacob

                Hi Jilani,

                 

                Have you tried using FIXED LOD ?

                 

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

                IF(REGION)=WEST

                THEN

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

                END

                 

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

                IF(REGION)=EAST

                THEN

                {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])

                  END

                   

                   

                  Hopefully this gives you what you were looking for.

                   

                  Walt

                  • 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.