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







            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


              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