3 Replies Latest reply on Apr 26, 2017 6:01 AM by Michael Hesser

# Create a filter or calculated field of specific date ranges?

Hi,

I'm not sure if this is something can be done in tableau, but what I'm trying to do is create a filter of a series of set date ranges.  I have 5 set date ranges right now that overlap:

October 10, 2014 to June 30, 3015

January 15, 2015 to September 30, 2015

April 15, 2015 to December 31, 2015

July 14, 2015 to March 31, 2016

October 14, 2015 to June 30, 2016

(these are the 180 non-holiday weekdays ending each quarter)

I am wanting to calculate the average vehicle volume during each of these date ranges and it would be nice to have a filter where I can easily click and compare between ranges rather than having to slide the date range bar to each of my desired date ranges every time.

What I have right now is two parameters: start date with my 5 start dates, and end date with my 5 end dates, and that is better than using the slide bar, but I still can't look at more than one range at once.

This screen capture shows what I already have and what I would like to have with each of the columns representing a the five ranges.

Is it possible to create a calculated field of set date ranges that overlap? Is this something that parameters can do?

• ###### 1. Re: Create a filter or calculated field of specific date ranges?

Hello Katie;

You can certainly do this! I recommend using SETS and LOD expressions.

Step 1: Sets

• Create 5 sets based on [Date]
• You can click for each date you want in each set, or you can save your finger and mouse button and use the CONDITION/By Formula to set your ranges. Your formula for your first range, for example, would be:

Step 2: LOD Expressions

• Now set up 5 LOD expressions. These will look like:

Range 1

{fixed: avg (iif[Set 1],[Volume],null)}

These LOD expressions are just going to average the volume based on the period in the sets you've created.

Step 3: Drop your new LOD Expressions onto your Rows

• Make sure you are using your entire date range
• You may need to experiment: I see you have some holidays and other things which may affect the results you see.

Hopefully that will get you the results you need!

• ###### 2. Re: Create a filter or calculated field of specific date ranges?

What is the purpose of the # in the formula?

• ###### 3. Re: Create a filter or calculated field of specific date ranges?

The # signs indicate that the value is actually a date.

The format in my example may not be correct: the proper format may be:

#2014-10-10# however I found Tableau quite flexible when using dates.

There are other ways you can specify dates, as well. For more info about date functions, check out this link: