Creating filters based on dates ranges

Question

I am new to Tableau and had a question about creating filters. I have a data set that has a field called "Start Date" and a field called "End Date". Using these two fields, I want to create a filter with the following options:

Records that have a "Start Date" prior to the current date, called "Pending Records"
Records that have an "End Data" after the current date, called "Expired Records"
Records that have a "Start Date" equal to or greater than the current date and have an "End Date" less than the current date, called "Active Records"

I would like this to be one filter, with those three choices. Is this possible? If so, are there any examples I could look at?

FYI, I am using Tableau Desktop Version 10.3

thanks

UPDATE

I also had to add a status for "Records with an end date within 6 months", so I used this code:

IF 
(
[Start Date] > TODAY()  
) then "PendingRecord"
elseif (
[End Date] < TODAY() 
) then "ExpiredRecord" 
elseif (
[Start Date] <= TODAY() AND 
([End Date] > TODAY() AND
[End Date] > DATEADD("month",6, TODAY()))
) then "ActiveRecord"
elseif (
[Start Date] <= TODAY() AND 
([End Date] > TODAY() AND
[End Date] <= DATEADD("month",6, TODAY()))
) then "EndingIn6Months"
END

This creates a filter with 4 options. But, "EndingIn6Moths" is really a subset of ActiveRecord. So, when a user selects ActiveRecords, it should also include EndingIn6Months. Is this possible? Is it possible to auto check a filter (EndingIn6Months) when the user checks ActiveREcords option?

Juanma Juanma · Accepted Answer · 2017-08-09T14:53:09

I think the following will help you.

1- Create a calculated field for current date : Now()

2- Create another calculated field

If  [Start Date] = [Now] then "Pending Records"
    ELSEIF      [END Date] = [Now] then "Pending Records"              
    ELSEIF      [Start Date] => [Now]  AND [END Date] < [Now] then "Active Records"                                                                     
    ELSE        "Others"
    END

Could you please share your workbook?

I feel like different rows will fall into different buckets. You will have cases when you will meet first and second condition.I think you would need to exclude them from each other. It would be great to see the dataset.

Please let me know if any question.

Creating filters based on dates ranges

1 Answers