9 Replies Latest reply on Jul 13, 2018 4:25 PM by Shinichiro Murakami

How do I find the amount of days between a "range of dates" filter?

Justin Happ Jul 9, 2018 10:23 AM

Hello, I am trying to find the amount of dates between a "range of dates" filter.

I have a date dimension as [Session Date], it uses a range of dates filter (e.g. 6/19/2018 - 7/13/2018).

Is there some LOD calculation that can give me the amount of days between these two filtered dates? And if so, will it be updated as the filter is changed?

For example, the answer I am looking for would result in the amount of days between 6/19/2018 - 7/13/2018 to be 24 days.

All assistance is appreciated, and I thank you in advance.

Justin

1. Re: How do I find the amount of days between a "range of dates" filter?

Vishnu Sekhar Jul 9, 2018 10:39 AM (in response to Justin Happ)
Hi Justin

Please find the attached workbook. Let me know whether this will work for you or else we can tweak it out to match the requirements.

Regards
Vishnu

Number of Days between Range of Dates.twbx 1.2 MB

1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
2. Re: How do I find the amount of days between a "range of dates" filter?

Justin Happ Jul 9, 2018 11:05 AM (in response to Vishnu Sekhar)

Vishnu,

Thank you for your help. I can confirm that this does fix my issue of finding the amount of days between two dates.

However, there is another predicament. For the dimension I am using (Market), there are some days where there are zero entries. For example, days 7/10/2018 and 7/11/2018 have no entries, and when I go to find the amount of days between 6/19/2018 - 7/11/2018, the value comes up as 20 days. This value should be 22, but it leaves out the two days in the calculation because there are no entries there.

I already clicked "Show missing values" in my table, but this is still an issue with the calculation "skipping days" if there are zero entries (Number of Records).

Please let me know if you have any advice on what to do.

Thank you once again.

Justin
Like Show 0 Likes(0)

Actions
3. Re: How do I find the amount of days between a "range of dates" filter?

Vishnu Sekhar Jul 12, 2018 7:43 AM (in response to Justin Happ)

Hi Justin

I was trying to resolve the issue you have mentioned but unfortunately was not able to find any resolution. Sorry for that.

Requesting help from stalwarts Jonathan Drummey Shinichiro Murakami to calculate number of days between two dates in the selected filter range, the number of days must include missing dates as well.

Regards
Vishnu
Like Show 0 Likes(0)

Actions
4. Re: How do I find the amount of days between a "range of dates" filter?

Shinichiro Murakami Jul 12, 2018 8:15 AM (in response to Vishnu Sekhar)
HI Vishnu and Justin

The easy solution is using LOD with context filter.

Tableau's Order of Operations

Thank you Vishnu for workbook preparations.

Thanks,
Shin

Number of Days between Range of Dates_SM_10.3.twbx 1.2 MB

1 of 1 people found this helpful
Like Show 2 Likes(2)

Actions
5. Re: How do I find the amount of days between a "range of dates" filter?

Vishnu Sekhar Jul 12, 2018 3:06 PM (in response to Shinichiro Murakami)

Hi Shin

Thank you for helping us out.

A quick question

Consider the dates 01/01/2014 and 12/31/2014. The above calculation you mentioned will return the number of days as 362. But as we know the year has 365 days. Is there anyway in Tableau to get 365 as number of days.

Regards
Vishnu
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
6. Re: How do I find the amount of days between a "range of dates" filter?

Shinichiro Murakami Jul 12, 2018 3:21 PM (in response to Vishnu Sekhar)

I think you specified to use "Filter" and user cannot pick 2014/1/1 because there is not such date in data.

If you need to get 363 instead of 362 (include both dates), you can add "1".

Shin
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
7. Re: How do I find the amount of days between a "range of dates" filter?

Justin Happ Jul 13, 2018 11:59 AM (in response to Shinichiro Murakami)

Shin,

Thank you for your response, it works!

Appreciate it.

Justin
Like Show 0 Likes(0)

Actions
8. Re: How do I find the amount of days between a "range of dates" filter?

Justin Happ Jul 13, 2018 12:00 PM (in response to Vishnu Sekhar)

Vishnu,

Thanks to you and Shin, I now have the answer to my question.

I appreciate your feedback and replies.

Justin
Like Show 0 Likes(0)

Actions
9. Re: How do I find the amount of days between a "range of dates" filter?

Shinichiro Murakami Jul 13, 2018 4:25 PM (in response to Justin Happ)

HI Justin,

You are welcome.
Glad that I could help you.

Shin
Like Show 0 Likes(0)

Actions