6 Replies Latest reply on Dec 11, 2016 7:24 AM by John Croft

# Date Difference

Hello,

I have data set as per below table, where by Receipt data do have few missing value.

I want to use a formula where by-

If receipt data is Missing then "No Receipt"

else today()-receipt date

Also based on the days, calculated by above formula I want to create a aging as per below

IF [Receipt Aged]="No Receipt" THEN "No receipt"

ELSEIF  [Receipt Aged]<10 THEN "Less than 10 Days"

ELSEIF [Receipt Aged]<51 THEN "10-50 Days"

ELSEIF [Receipt Aged]<81 THEN "51-80 Days"

ELSEIF [Receipt Aged]<101 THEN "81-100 Days"

ELSEIF [Receipt Aged]<151 THEN "101-150 Days"

ELSEIF [Receipt Aged]<201 THEN "151-200 Days"

ELSE "Above 200 Days"

END

Receipt Date
PO Date
10/23/201610/23/2016
10/11/2016
10/01/2016
11/01/201611/01/2016

Thanks

Arihant Jain

• ###### 1. Re: Date Difference

Hi Arihant -

Are you asking which Tableau expressions to use? My advice would be for you to start by reading "Help" on Tableau's functions...but look for functions like datediff(), ifnull(), isnull(), if and iif().

Play with these a little and see what you come up with. Post your attempt at making this work using the data above and others will help you...but please do make an initial effort yourself.

• ###### 2. Re: Date Difference

HI Russell,

I did initial effort and am still trying to figure it out. I have used ISNull, IFNull and infact converted the date data in string format and tried, but I am not able to get the formula correct.

Simply using Today()- Receipt date, so consider the blank receipt date in calculation.

Help me if you can.

Thanks,

Arihant Jain

• ###### 3. Re: Date Difference

Got a sample packaged workbook?

• ###### 4. Re: Date Difference

I think you are looking to achieve this...

So you can set up the Receipt Aged Calc like this:

And then set up the Agging Buckets like this:

You'll need to coerce strings and floats to make this work since you have NULLs you want to rename as a string when dealing with integers. And then float the numeric strings back to integers. Hope this helps. Let me know if you have questions.

• ###### 5. Re: Date Difference

Thanks John with the formula this is working perfectly. Btw, what does float implies and when can we use it.

Regards,
Arihant Jain

• ###### 6. Re: Date Difference

It allows you to cast a 'string' variable' in a numeric context as long as it makes sense. Here's a Tableau link to also review. Type Conversion