Somewhat akin to what I wrote about lately, today we will be focussing on a question that was raised in the forums today.
The asking person wanted to use the natural date hierarchy drill down (i.e. using the little plus / minus icons on date fields) to drill from year to quarter, month etc.
This:
At the same time, the request was to always show the closing values of the final day of the selected period.
The solution
Solving this is actually not very complicated, all it takes is playing around the order of operations.
We will start with providing the solution and for those interested in the “whys” I will provide an extra section below the solution.
I am using a random data set created in excel with dates and a value column named “closing”.
So, here is all we need. For last of the shown period – in the example of the screen above the last of each month – create this calculation:
// closing val at end of period
if [Date] = {include : max([Date])} THEN [Closing] END//
For the values at the beginning, we would just swap max for min and be done.
And that is actually already all we need.
Let’s check on some of the numbers
Now we drill to month.
Note Q1 2022 closing value equals exactly what we see as closing value in March.
And if we go to Day level it still matches:
So, if you just wanted the “how to” then you can stop here.
If you are interested in more, feel free to read …
Why does this work?
The basic idea we use here is to make use of the order of operations. By not including any dimension in our include function we actually do not change anything. We are not adding any extra granularity to the view (or rather: to the calculation in addition to what is in the view).
What happens is that we basically create an invisible column against which we can now perform row level calculations (as we can for all types of LOD)
This means, we are now in a position to compare every date against the max date (or min, depending on your measure) derived from the view.
Since we use the include function w/o any further specifics, this means it will always give us the max date at the granularity stipulated in the view. And the view’s granularity is based on the drill down we look at.
Why can’t we simply use max([date]) ?
A max([Date]) function is an aggregation that will not allow us to compare all the dates on a row level against the result. We cant do that
In SQL terms, the max date function here is like a
SELECT
XYZ,
max(DATE)
from source
group by 1we can’t compare an individual date to the max date as at the moment of executing the max date is not yet known. Both would be happening at the same run time which is not possible.
In turn, our INLCUDE function will result in an inner join (screen shows only an extract of the query)
How about using FIXED?
We can’t use fixed LODs here as a fixed LOD ignores the granularity provided for in the view. It does not add or remove granularity that is actually imposed by the dimensions in the view for a specific calculation but rather completely calculates at it’s own level of granularity.
This means, if we do something like
// fixed example 1
{fixed datetrunc('quarter', [Date]): max( [Date]) } then we’d be getting the max date for every quarter but that would do us no good obviously because we do not need the quarter in many cases. Here is looks ok:
But as soon as we drill up or down:
A table scoped FIXED would also do no good (this is a fixed calculation w/o any specified dimension) as it would result in the max date in the entire underlying data table.
Can we use exclude?
Short and simple: yes, we can. Does not make a difference whether we include nothing or remove nothing, effect is the same. All we want is that nice little inner join to be able to do row level calculations.
Find the workbook in my tableau public.
And that’s it for today. I hope you find that useful.
As always appreciate your feedback.
Until next time.
Steffen
Tableau Adventurer 