7 Replies Latest reply on Mar 29, 2011 3:48 AM by Mel Stephenson

# Display What Percentile A Value Represents

I'm working on some production data and produced the attached viz to show the range of times of how long it's taking us to produce consumables boxes for our disc repair machines. Discussing the results with my team, they very reasonably asked if the viz could instead tell them what percentile of each day's production was produced in x seconds or under (essentially the reverse of the current reference lines). So, given a target production time, they can tell what percentile of boxes are produced within that time or less.

Can anyone help?

• ###### 1. Re: Display What Percentile A Value Represents

How about something like the attached?

• ###### 2. Re: Display What Percentile A Value Represents

Many thanks for that, Joe. As beautiful and thorough as ever.

*But* (sorry about this) is it possible to maintain the format of the original and the only change to be that the displayed value is the percentile that are within target time? Keeping the level of detail at the box id allows us to use this panel as a filter to click through to the detail of individual outlier boxes - a feature we would lose with the other formats.

Incidentally, the formula for '% At or Below Target' is

SUM(IIF([Interval]<=[Target (seconds)],1,0))/SUM(1)

What does the '/SUM(1)' part do?

Many thanks in advance

• ###### 3. Re: Display What Percentile A Value Represents

So from your original, you want to replace the the text for each reference line with the count. so have a ref line at the seconds amount, but display the count. My first thought is to use a dual axis, I'll see if I can mock something up.

As for '/SUM(1)', that is just like 'SUM([Number of Records])', or the total count of records for that mark.

• ###### 4. Re: Display What Percentile A Value Represents

That's a nice way to represent a distribution - it's quite common to have a target expressed like that and what to know how many (or what proportion) met the target - so I had a bit of a play with it.

Joe was quite right, you can do it with dual axis.

I've attached a tweaked version of Joe's.  There were a couple of tricks to get that to display right.

I created a calculated field which just has the value of the [Target (seconds)] parameter, and used that as the secondary axis by adding the [Target (seconds)] calculated field to Rows.

Initially the "Synchronize Axis" option was greyed out - which was because the primary axis was a floating point number and the secondary axis was an integer.  Just defining the [Target (seconds)] calculated field as FLOAT() of the parameter sorted that out.

I made that axis a Gantt, removed [Box Id] from LoD and added [%At or Below Target] as a label.

I also defined a reference line on the secondary axis just showing the value of the [Target (seconds)] calculated field.  That let me label the target line and also show the daily %ages.

Finally I set transparency to zero for the Gantt bars - they were just overlapping the reference line and don't need to show.

I don't know of any way to make the mark labels show up better against the main marks, in the way that the reference line label works.

• ###### 5. Re: Display What Percentile A Value Represents

That's a beautiful piece of work. Thank you Joe. Thank you, Richard. That's exactly what I was looking for (along with a few extra touches that I would almost certainly not have put in).

Now to brew a cup of tea and patiently work my way through how it all goes together!

All the best. Mel.

• ###### 6. Re: Display What Percentile A Value Represents

The attached is likely not what you wanted, but I thought it would be a fun exercise, and make use of Richards TCRL entry for quartiles: http://www.tableausoftware.com/tcrl/table-calculation%3A-quantile

I took your original, and added the count of marks that are at or below the 95th Percentile. So you have the Interval value at the 95th Percentile, and the count that are at or below the 95th Percentile.

The text display would be clearer with another combination of mark types.

• ###### 7. Re: Display What Percentile A Value Represents

Well I'm glad *you* had fun. Over on this side of the pond it was more cups of tea and two of us carefully picking our way through the workings with a slow succession of aha moments culminating in finally seeing how it all goes together - sheesh! Thank you, though, for doing this. It was a really enlightening exercise for us as it pulls together a number of different calculation functions. I'd recommend others take the time to dissect the calculated fields in this too - really useful.

Thanks again for this.

Mel