OK, I accept the challenge.
Will have a look tonight my time...
1 of 1 people found this helpful
Had a quick look at your data and you with that sort of volume of data you can actually achieve (roughly) what you described with reference lines. How far does this have to scale (i.e. what are your real data volumes).
One downside with that approach is that you can't use a continuous date axis.
Here are a couple of screenshots (2nd one is zoomed) showing what you can just do with point and click.
Thanks for your help.
The data that I sent is actual values we will be using so the values does have a broad range
I thought doing a reference line would work too but when I select "Value" as disaggregate measure I cannot add a reference line for each of the month (I can however do a reference line for the value Y Axis - it does allow me to do the reference line but i need the reference line to be different for each month)
How were you able to add reference line to the date axis? SOrry for being such a newbie on this... i have only been using Tableau for about a month
> Sorry for being such a newbie on this...
You don't have to apologise, we all started sometime.
Look at the screenshot below. The key thing is to make the date pill discrete, that allows you to get a reference line per month by selecting Per Cell. that means the gaps for the weekends get compressed on the chart. If you don't like that, I think you have to go with the table calculation approach. I'll have a quick go at putting that example together tonight, (if I remember).
I've attached a workbook showing a few ways of representing this - including one based on the quantile table calculation function I posted a while back (which requires a copy of the calculation for each quantile rank you want to show), plus a slightly different approach to displaying multiple quantiles.
The one showing 0, 20, 40, 60, 80 and 100 percentile reference lines was intermittently not displaying properly for me just now. It should have different coloured bands visible. If it's just white space with a few dots and lines, try selecting edit reference line and then don't change anything and see if it redisplays properly. Not sure what's going on there.
Both the table calculation approaches make use of a parameter controlling the precision of the answer (that is just a floating point number - so 10 means rounded to the nearest 10, 0.01 means rounded to 2 decimal places, etc). The higher precision you select, the longer the calculations take to evaluate. What I normally do is set the precision relatively low while I'm exploring the data (say 10 with your data), then when I want to publish the results maybe set it more accurately. With your data volumes you can actually get away without the rounding - though it takes 20 seconds or so to refresh the views, whereas with rounded data the visual effect is almost identical but the screen refresh is virtually instant.
On the quantile rank view, you can select the quantile you want (percentile, decile, quintile, quartile, etc). For the work I do I find the percentile rank "heat map" (below) a really effect view.
Quintiles_Help_RL.twbx.zip 5.9 MB
Thanks - this is exactly what i needed.
I've added a couple of sheets showing how to get tabular monthly data using either the individual quantile calculated fields or using the quantile rank approach. I just duplicated the two views using those approaches and reformatted a bit.
A couple of points.
I don't understanjd why you have the max and min columns under each month. Ive just shown the max and min along with all of the quintile values in the one column.
Qunintiles are the numbers that divide the points into 5 equal sized sets, so there are only 4 quintile values (which are the same as the 20th, 40th, 60th and 80th percentiles. The 0 and 5 entries correspond to min and max.
Does that make sense?
Note that you cab experiment with the impact of the precision parameter on the accuracy of the results (you may have to change the number formatting to see decimal places). Note that you can also select whether you want quartile, quintile, decile or percentile.
Quintiles_Help_RL2.twbx.zip 5.9 MB
OK, I see what you want - I'll have a play and see if I can work out how to display it like that.
Just a couple of clarifications.
Firstly (and this me being a bit pedantic), as I said before, the quintiles are the values at the boundaries between the sets of 20%, not the bands. So the crosstab I gave you in the last postings have all the values you want: The lowest 20% goes from the overall minimum to the first quintile, the next band goes from the 1st quintile to the 2nd qunintile, etc.
The other thing is you seem to have a special bonus offer in April - you get an extra free quintile. I don't think I can get Tableau to make the numbers add up to 120% (or at least I'm not prepared to try).
OK, you've got me scratching my head a bit here a bit here - I can't immediately see a way to do this.
What you want is a Gantt bar with the start of the bars defined by the quintile values and the length of the bars defined by the difference between the adjacent quintiles.
Trying to do that with the version where I have individual calculated fields for each quintile runs into the problem that a calculated field can't refer to the value of the [Measure Names] special field.
Trying to do it with the quantile rank version runs into the problem that table calculations can't partition on the results of another table calculation (in this case it needs to reference quantile rank).
Let me ponder for a bit...
Here you go - just needed another table calc...
I'm glad you persisted with the question - that's ended up being a really useful view. I especially like the fact that you can just select the quantile you want with a parameter and get your bar broken into the right number of sections. The legend could probably do with a bit of tweaking to make it more user-friendly, but other than that it looks really good, I think.
Here it is (zoomed in because the outliers obscure the detail) for quintiles:
and here for deciles:
Quintiles_Help_RL3.twbx.zip 6.0 MB
Thanks Richard!!!! You have helped A LOT!!! This is great -