Hi Jonathan, thank you for helping.
Yes, it is something like your example with a horizontal bar in 3 parts (blue, gray, orange).
"Do you want one bar for all month/year combinations or one bar per year?"
Answer: One bar for all month/year combinations, namely something imitating a stepped color legend.
Visually I thought about something similar to this:
Examples show filter for 10% and 15% drawn manually in an Excel sheet
Example Calculations 10%
- Blue range: FEWEST (MIN month 132 to MEDIAN month 168 * 0.9)
- Gray range: OTHER (MEDIAN month 168 * 0.9 to MEDIAN month 168 * 1.1)
- Dark gray line: MEDIAN month 168
- Orange range: MOST (MEDIAN month 168 * 1.1 to MAX month 223)
With this calculation method the months are not applicable as borders between Fewest & Other and Other & Most.
The more I think about, the more I prefer your idea
On second thought, I think a visualization with all months similar to your example is even more useful and also easier to make.
If so, this color legend viz should show numbers (reference lines) for:
- Low & High month of "Fewest"
- Median month
- Low & High month of "Most"
The Goal of the Visualization
The goal of the view (at the insurance company where I work) is to see if certain weeks/months generally (year after year) have low/high peaks of work done. My real dashboard shows claims opened (created) per week (upper viz as heat map) and month (lower viz as highlight table) and various filters.
- Identifying low peaks helps planning, such as relocating workers to other tasks in low-peak periods
- For high peaks it is the opposite (such as postponing other tasks with less priority)
Thank you, this looks perfect. I look forward to study your solution and implement it in production.
There is still some fine-tuning needed.
Need to have (partly solved):
1a) "Lower Other" must be >= MIN
1b) "Upper Other" must be <= MAX
Here is the result with parameter "% from median" set to 20% and 35%:
The lower bar with 35% from median should have MIN 132 and MAX 223.
I think 1a and 1b are solved correctly in the attached workbook (v3).
The "only" annoyances I have noticed are:
1) too bold numbers when 30% from median is chosen (not solved)
2) start of lower other is blue (rather than gray) and end of higher other is orange rather than gray (not solved)
Need to have (not solved):
2a) Some outliers in my production data set are so big that "fewest" and "other" are hard or impossible to read. Therefore I want to "cheat" with the length of the bar to maximum MEDIAN * 2.5 (or thereabout) - but still show the true value of "Most" (MAX), such as 223 in example above.
The x axis can still be continuous since "Show Header" for all columns and rows are deselected. The cheat value (text) is (I think) limited to Mark > [Mark Label] > [Mark Label Text].
I think some "cheating" is okay, because the purpose of the "color legend bar" is to make the categories "fewest, other, and most" more understandable.
Nice to have (not solved):
2b) Possibly there could be a dotted white line near the end of "Most" indicating that cheat for length of bar is activated. But this dotted line is a luxury which I can be without.
1 of 1 people found this helpful
As for your points under 1), see the attached, you are correct in your edit, and I just applied it to the others with similar logic. To remove the thin orange most, I used another formula to detect when this is so, and not show it.
As for everything under point 2), I was not able to understand what you are looking for. Can you provide sample data that recreates the situation? (does not need to be real, just representative). Also a mocked up image, like an edit in MS Paint, would be helpful in understanding what you are looking for in a final result, like you did before.
Extreme outlier added
I have duplicated the last row in the Tableau Excel data set approx. 200 times to create an extreme outlier.
See new example in attached workbook (2a) (solved)
The attached workbook shows my "color bar legend" with "length cheat" activated.
This is done by modifying your formula for "index 5 and 6" in [Mark Label] and [Mark Value], and also added a condition in [Mark Label Text].
My corrections are only qualified guessing using the trial error method, because I do not fully understand your code, although I trust it since I can see that it works as intended.
Therefore I hope you will look through the code to correct and optimize it, not only for me, but also others that might use this solution.
I do not expect the code to be bug-free yet, although my tests so far have not provoked any error.
The dotted line (2b) (cancelled)
I think the dotted line confuses more than it helps. Since I cannot think of any good way to do this, I rather cancel my wish regarding this. The length of the cheated (shortened) bar is still much longer than the rest when shortened to median * 2.2 and therefore signals that some big outlier exists. This is good enough for the purpose of the "color legend".
Cheat length formula alternatives
"Cheat length" does not have to be median * 2.2.
Other options could be a calculation that ensures that:
a) "Median" is never positioned in first x % of bar, such as first 30% or
b) "Most" never occupies more than 60% of the bar
For now median * 2.2 is acceptable for the data in my production dashboard.