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

# Calculation logic for the date functions

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

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)

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

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

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