8 Replies Latest reply on Jun 6, 2017 10:58 AM by Joe Oppelt

Calculated field using the start and end of the date range

Lets say I have 6 months of subscriptions in my database which I started from Jan 1 2017 (Starting date) till June 30th 2017 (end date). Lets say 1000 subscriptions in this period

I have a date range of subscription date which filters the date of subscriptions within Jan 1 2017 till June 30th 2017. Lets say the date range is currently set to March 1, 2017 to March 31st, 2017

I need to make a calculated field which uses the first and last date of the date range.

i.e.

Starting Date = March 1, 2017

End Date = March 31st, 2017

If I change the date range, the staring and ending date changes accordingly (and should reflect in the calculated field)

In the calculated field:

I want to

1) count the subscriptions from day 1 (Jan 1st 2017) till the starting date (March 1st 2017)  - Of-course the starting date changes according to the set filter

and

2) count the subscriptions from Ending date (March 31st 2017) till the last date (June 30th 2017) - Of-course the Ending date changes according to the set filter

Is it possible in Tableau?

• 1. Re: Calculated field using the start and end of the date range

Yes The first point is very straight forward and possible.

1. You want to know number of Subscriptions in some particular time period. Say the time period is between Ref_StartDate, Ref_EndDate. Add these two items as "Parameters"

2. Create a new Filed "Count" with the equation: IF (([Start Date]>=[Ref_StartDate] and [Star tDate]<=[Ref_EndDate]) or ([EndDate]>=[Ref_StartDate] and [EndDate]<=[Ref EndDate]))  then 1 else 0 end

this will give you the answer to the first question and for second one you can replace startdate with EndDate and try, i am about to leave office otherwise would have shared a workbook with you.

• 2. Re: Calculated field using the start and end of the date range

Hi Nishant,

I have tried putting the paramters and it works well.

Here I want a single universal filter (which is used for other charts as well in the dashboard). So I want to use the range date filter and fetch the starting and ending date of THAT filter in the calculation field.

Other than putting a separate parameter which is an extra work to do (we change filters range quite often so using a filter and a parameter for a single view is not efficient).

Is there a way to accomplish it with a single date range filter?

• 3. Re: Calculated field using the start and end of the date range

"You want to know number of Subscriptions in some particular time period."

No I dont want that.

I want to use the filter to get the from very first day till that starting date of filter.

Then from ending date of filter to the very end date of database

In other words, excluding the date range, 0 to date range start and date range end to very last date

• 4. Re: Calculated field using the start and end of the date range

and yes, I want make use of the existing filter

• 5. Re: Calculated field using the start and end of the date range

You can set a filter to EXCLUDE, and it will eliminate whatever is selected and leave the rest.

• 6. Re: Calculated field using the start and end of the date range

That will not solve the problem. I just explained a very simple case, and exclude will not solve it.

I need to use the existing date range filter's starting and ending date in a calculated field. Its a must.

Is it possible?

• 7. Re: Calculated field using the start and end of the date range

By the way, As I mentioned, I want to use one filter for many charts. Lets call it universal filter. so using exclude for only one chart will mess up other charts in the dashboard.

• 8. Re: Calculated field using the start and end of the date range

The problem with a filter is that it will either eliminate the rows that are outside the range, or (if you do EXCLUDE) will eliminate the rows within the range.  That's what filters do.

Parameters were mentioned, but you have nixed that approach.  It's the approach I would take.

If you have robust data (at least one row for every possible date) you can do a calc to get the MAX date and MIN date on the sheet, and that would get you the specific range the user selected.  (If you are missing some dates and the user selects a date that doesn't have a row in the data, MAX or MIN will give you the next highest/lowest value, but not the specific date the user selected.)  Again, though, this will eliminate the rows that the filter eliminates.