How to show the selected time frame and a warning if it is broken

A quite common request is to display what the user has selected within a filter.

In our today’s case what we will do is to show within the header the selected date range plus a warning if the dates have a break in it. That is, if the user selects January, February and April, the headline will still show “Dates selected: January to April” but also give a warning that the timeline is broken, like this:

Notice this already implies we are not going to use a date range filter since this will never be broken but rather individual dates to be selected.

First, consider this

Define the date range

In any case we first need to determine the selected range. We do this by creating two calculations. In my example, I am using superstore data and my granularity is the month of order date.

//maximum date selected
window_max(max(DATETRUNC('month', [Order Date])))

//minimum date selected
window_min(min(DATETRUNC('month', [Order Date])))

My example table simply has the sub categories on rows and sum of sales as the measure

Put the two just created calculations onto the Detail card. You can leave the “compute using” as is, i.e. “table down”.

Double click your title to edit it and insert the fields like this:

Result:

So far, so good.

On to the second step..

Include the “Break Warning”

Let us start with the case where we do not have the months of order date in our view, so the table is as is shown above. Then, we can create this calculation:

//Warning breaks
IF window_max(COUNTD(DATETRUNC('month', [Order Date])))
<
DATEDIFF('month',[minimum date selected],[maximum date selected])+1
THEN "Selected Period has breaks in it"
ELSE ""
END

What we do is we check if the distinct count of months in the view is smaller than the date difference between the minimum and maximum date. We add +1 because as an example if we have March and April selected, the count distinct would be two. The date difference in months however is only one so we need to add one.

Put this calculation on the details and add it in the title:

If we now deselect a month we get what we want.

Selecting a time series without breaks:

What happens if we want to have the months also in the view?

Now let us assume that we want to show the months also in the table like this

Notice that already we get a warning which simply is not correct as our selected time frame is not interrupted at any point.

The first idea one might have is that this is because we have less months in maybe one of the sub categories (tip: bookcases for example only has January, March and April 2019 but no February). But that is not the reason.

The problem we are facing is this part of our calculation

window_max(COUNTD(DATETRUNC('month', [Order Date])))

This is a table calculation and no matter how we set it, now that Months of Order Date are in the view, it will always be considered, either as the partition or the addressing.

If we partition by Sub-Category (i.e. not select it) the calculation will still address the month of order date

So “within every sub-category for every month give me the max”. When it is “for every month” that means the count distinct can only be one every time because it is calculated for every month individually.

You can give it a try, no matter what we do, the problem is always that either the month is the partition and thus in itself will always return one, or it is the addressing and thus “for every month” we will calculate, again returning one as the result. Therefore, the only time the wrongful header will not be displayed is if we use only one month because only then the datediff calculation will equal the distinct months.

Another problem when using totals with months in the view

If we include sub-totals in our table and our months of order date are in the view, instead of seeing the wrong warning, we now get an ugly “all” header that we cannot get rid of

How to solve these problems?

Well since the months of order date are out problem we must get rid of them in the calculation. Luckily, that is what the EXCLUDE LODs are for.

Adjust your calculation to this

//Warning breaks | EXCLUDE
IF window_max(max({ EXCLUDE [Order Date]:  COUNTD(DATETRUNC('month', [Order Date]))}))
<
(DATEDIFF('month',[minimum date selected],[maximum date selected])+1)
THEN "Selected Period has breaks in it"
ELSE ""
END

Put this on the details and select it in the title and all will be good. No “all” showing if we use subtotals and no wrong warning when actually we do not have any breaks.

So, this is all working as we wished. Nice!

a pitfall to avoid

When creating the warning calculation with the exclude function be very careful not to confuse max with sum! If you conflate the first row of the calculation by doing this

IF window_max(sum({ EXCLUDE [Order Date]:  COUNTD(DATETRUNC('month', [Order Date]))}))

instead of this

IF window_max(max({ EXCLUDE [Order Date]:  COUNTD(DATETRUNC('month', [Order Date]))}))

and have another dimension in the view like this

we can see that now instead of showing the warning we get “all” when our time frame is broken.

So make sure you are very careful when creating the calculations.

Here is the Tableau Workbook

And that is it for today, I hope you find that useful.

As always appreciate your feedback.

Until next time.

Steffen