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?

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?

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

1 of 1 people found this helpful
• 2. Re: How do I find the amount of days between a "range of dates" filter?

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

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

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

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

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

1 of 1 people found this helpful
• 5. Re: How do I find the amount of days between a "range of dates" filter?

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
• 6. Re: How do I find the amount of days between a "range of dates" filter?

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
• 7. Re: How do I find the amount of days between a "range of dates" filter?

Shin,

Thank you for your response, it works!

Appreciate it.

Justin

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

Vishnu,

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

I appreciate your feedback and replies.

Justin

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

HI Justin,

You are welcome.