7 Replies Latest reply on Dec 19, 2013 7:50 AM by Banusaranya Duraisamy

# How to find the date difference with one date field when a measure field exceeds certain value?

I have one date field and I want to find the number of days it takes to exceed the amount 50,000 within the partition. Please find the Explanation below. I need to generate the column 'Date Diff' in tableau.

 ID Currency Trade Date Amount Date Diff Explanation 123 EUR 3/24/2013 80,500 1 first occurrence that exceeds 50,000  within the partition so 1 123 EUR 5/8/2013 50,000 0 <= 50,000 so 0 123 EUR 5/25/2013 60,250 45 diff  between last occurrence of the date when the amount exceeded 50,000. i.e  datediff(5/25/2013,3/24/2013) 123 EUR 6/24/2013 20,000 0 123 USD 4/3/2013 120,200 1 first occurrence that exceeds 50,000  within the partition so 1 123 USD 5/13/2013 20,000 0 123 USD 6/12/2013 60,150 51 diff  between last occurrence of the date when the amount exceeded 50,000. i.e  datediff(6/12/2013,4/3/2013)
• ###### 1. Re: How to find the date difference with one date field when a measure field exceeds certain value?

I would appreciate any sort of idea on the above requirement.

• ###### 2. Re: How to find the date difference with one date field when a measure field exceeds certain value?

This can definitely be done, however I'm confused about exactly what you want.

- What defines the partition? I'm assuming it's the ID and currency together?

- Are you looking for the first occurence when the running sum of the amount within the partition is >= 50K *or* the first occurence of an amount that is >= 50K?

Jonathan

• ###### 3. Re: How to find the date difference with one date field when a measure field exceeds certain value?

- ID and Currency together defines the partition

- Date Difference between the amount that exceeds the 50K with the previous amount that exceeded 50K within the partition.

Thanks

Banu

• ###### 4. Re: How to find the date difference with one date field when a measure field exceeds certain value?

What should be returned if no or only 1 entry exceeds the 50K cutoff?

• ###### 5. Re: How to find the date difference with one date field when a measure field exceeds certain value?

The entries less than 50K can display 0 and if only one entry exceeds 50K then it should display 1.

• ###### 6. Re: How to find the date difference with one date field when a measure field exceeds certain value?

The requirement is to find the number of days it takes to exceed 50K within the partition.

• ###### 7. Re: Re: How to find the date difference with one date field when a measure field exceeds certain value?

Issue Resolved.

Step 1. Created another calculated field for Trade date with following expression

if [Running Amount] > 50000  and lookup([Running Amount],-1) > 50000

elseif [Running Amount] > 50000 and lookup([Running Amount],-2) > 50000

elseif [Running Amount] > 50000 and lookup([Running Amount],-3) > 50000

else

Step 2. I did date difference based on the following condition

if ([Running Amount]> 50000 and ATTR([Trade Date])=[Trade Date Lookup]) then 1 else