3 Replies Latest reply on Feb 21, 2017 9:44 AM by Jilani Shaik

    Calculation logic for the date functions

    Jilani Shaik

      anyone plz help me in the following calculation

      in the above data i want to calculate gap period for the  same id repeated in multiple regions

       

      the calculation will be if region is west and order id is 100 same order id for east region also

      in that case west region gap period will be as follows

       

      west accept date - east complt date for order id 100 and same for the id's 102,103

      the result will be ( west accept date-> 7/7/2013 -  east complt date-> 5/30/2012) == 404 days

       

      if order id not repeated in multiple regions then no need to calculate like id=101

       

       

      plz help me asap

        • 1. Re: Calculation logic for the date functions
          Norbert Maijoor

          Hi Jilani,

           

          Find my approach based on lookup-function as reference below and stored in attached workbook version 9.3 located in the original threat.

           

           

          How did I approach?

           

          a. Step 1: Defined Order ID copy

          Order ID copy: LOOKUP(attr([Order ID]),-1)

           

          b. Step 2: Completed adj

          Completed adj: lookup(attr([Completed]),-1)

           

          c. Step 3: Delta

          Delta: if attr([Order ID])=[Order ID copy] then DATEDIFF('day',[Completed adj],attr([Accept])) else 0 END

          5 of 5 people found this helpful
          • 2. Re: Calculation logic for the date functions
            Jilani Shaik

            Hi Norbert

             

            Thanks for the quick response.

             

            But the region order is

             

            East

            west

            north

            south

             

            here the east alwasy be 0

            west will be   west accept - east complet

            north will be north accept - west complete

            south will be south accept - north complete

             

             

            but the region hierarchy will not always be the same

             

            some times only west, north, south and west,south and west, north and north,south

             

            in that case we will miss the east complete date

             

            Could you please tell me some more detail

            • 3. Re: Calculation logic for the date functions
              Jilani Shaik

              hi norbert can u help me in this

              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