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

# Need Logic for Date functions

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

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

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

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

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

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

Hi walt