Ken Flerlage could you take a look at this viz for me? This is where my question about LOD Expressions stemmed from this morning.
What I am trying to do is to compare a 2018 Fund Value to a 2019 Fund Value.
The idea for this viz was to use two pieces of logic that Ryan Sleeper teaches:
1. Benchmark Technique using an LOD Expression = i.e. {EXCLUDE [Fund]: SUM(IF [Fund] = '2018 Fund' THEN [Amount] END)}
2. Date Normalization = Dates that are associated with a 2018 Date = Add 1 to the year to bring them up to a 2019 value.
I am placing my transaction dates on a normalized axis rather than a traditional date axis. The reason is that I am most interested in seeing Fund 2018 Month 1 as compared to Fund 2019 Month 1.
Where I am having an issue:
In the tooltip, I want to show the transaction date for the 2018 Fund rather than the normalized date.
When I hover over a mark – it would be nice to show:
Transaction date
(i.e. for a 2019 Fund – 2019 Transaction Date, for a 2018 Fund – 2018 Transaction Date
Value for the 2018 Fund
Value for the 2019 Fund
Difference between 2018 and 2019 Fund
The trouble occurs when you try to show a 2018 Value on a 2019 Mark. That makes sense but I would think you could get an LOD expression to help here.
I went to Tableau Community and they suggested using a LOD expression like this: {FIXED DATETRUNC(‘month’,[Date (Normalized)]:SUM[c. Source of Money]}
I did try to implement that type of a solution but I found that fixing the expression is too high of an aggregation because of the organization name (Org) that has to be included. Granted I should have mentioned this in my original example but didn't.
I am working on a hierarchical order and if the answer is to fix it at the fund and org level that's fine. Then I would have to build that calc for each level in my dashboard - no sweat.
Thanks in advance for your help!
Scott
Got it. Ideally, you'd just put Date on the tooltip card and that would allow you to see the date. But that gives you this:
You get the dreaded asterisk. This is because there are actually two dates associated with the record we're dealing. And this is because you're using Measure Values and Measure Names to display the separate lines. And, unfortunately, you can't use either of these in a calculated field in order to get just the specific date for that line.
So, to fix this, we really need to somehow use a dimension in your data rather than Measure Names for the color. If I understand your data correctly, I think the following should do that:
I've created a filter on Source and made it a single-value dropdown. I've then used your fake date on columns and Amt on rows. Most importantly, I've created a calculated field called Fund Year which looks like this:
// Pull the year portion from the fund.
INT(RIGHT([Fund],4))
Because we're using Fund Year, which is a singular dimension, to color the lines, we can now drag Date to the tooltip card in order to show the actual dates.
But, you mentioned that you wanted to show the 2018 and 2019 values, and difference, in the tooltip. With the above approach that would require a LOD because each line shows only 1 of the 2 years, but we want to show both at the same time. Thus, we have to step outside of the viz level-of-detail.
Before creating the calculations, we need to right-click on the Source filter and add it to context. This is because we want to make sure that this filter is applied before the LODs are calculated (i.e. we want the LODs to take the filter into account, not ignore it). See the Tableau Order of Operations for more details on this: Tableau's Order of Operations - Tableau
Now create the following calculated fields.
2018 Value
// Value for 2018.
{FIXED [Org], YEAR([Date (Normalized)]), MONTH([Date (Normalized)]): SUM(IIF([Fund Year]=2018, [Amt], NULL))}
2019 Value
// Value for 2019.
{FIXED [Org], YEAR([Date (Normalized)]), MONTH([Date (Normalized)]): SUM(IIF([Fund Year]=2019, [Amt], NULL))}
Difference
// Difference between 2019 and 2018
SUM([2019 Value]) - SUM([2018 Value])
Drag all three of these to the tooltip card and clean up your tooltips so they look how you'd like them to.
In the end, I believe you should have what you need:
See attached.
