1 2 3 31 Replies Latest reply on May 23, 2017 2:13 PM by Shawn Wallwork Go to original post
• ###### 15. Re: Box Whisker Plot along with Time of a Day in Y axis

Rajeev, first thanks for mentioning the version # in your original post. That certainly helps us. However I simply can't get myself to work in pre-9.x versions. It's just too painful. But I am attaching a Hacked 8.2 workbook. See attached.

If you are going to continue to work in 8.2 you'll want to know how to hack workbooks back to your version:

Re: Downgrade from 9 to 8.3

Cheers,

--Shawn

• ###### 16. Re: Box Whisker Plot along with Time of a Day in Y axis

Hi Rajeev,

Since you pinged me I thought I'd let you know - the transformation technique I outlined in Formatting Time Durations in Tableau | Drawing with Numbers is not for continuous axes. The reason why is that it's using number formatting and Tableau draws axes using Arabic numbers that are base 10 so a seconds axis will go from 0 to 99 and restart when what we'd really want is for the seconds  to use the Babylonian base 60 numbering so we'd want the seconds axis to go from 0 to 59 and restart. Here's an example:

Jonathan

2 of 2 people found this helpful
• ###### 17. Re: Box Whisker Plot along with Time of a Day in Y axis

Jonathan Drummey: Thank you for sharing this useful information with us , I was not aware at all. I will definitely use this knowledge from now on.

Also, Thanks for including the screenshot  as well.

Merry Christmas and happy new year

• ###### 18. Re: Box Whisker Plot along with Time of a Day in Y axis

Dear Shawn Wallwork

I followed your approach but still no luck.

So I am attaching the sample data in the form of excel sheet.Every information is mentioned in the excel sheet like Start time, End Time ,Duration in Minutes etc.

Mostly My job completes on below mentioned hour (in between ( 2 AM, 3 AM, 4 AM , 22 PM , 23 PM). )

But I want my graph looks like the below graph .Y axis should show the exact end time when Jobs got completed.

Note: The below graph is just to represent my  expected output ( This Y axis is nowhere closed to my desired result.)

• ###### 19. Re: Box Whisker Plot along with Time of a Day in Y axis

Rajeev, I'll take a look, but I'm confused by this statement:

But I want my graph looks like the below graph .Y axis should show the exact end time when Jobs got completed.

Note: The below graph is just to represent my  expected output ( This Y axis is nowhere closed to my desired result.)

Is this your expected result or not? What do you mean in the part I underlined?

Thanks,

--Shawn

• ###### 20. Re: Box Whisker Plot along with Time of a Day in Y axis

OK, I think I finally understand what you are going for (I think). Is this the chart you are looking for?

I am assuming that you really were not wanting/needing this chart to use the job durations, but that you thought this might be a way of getting the Box & Whisker distribution chart to work. Is this correct?

Cheers,

--Shawn

• ###### 21. Re: Box Whisker Plot along with Time of a Day in Y axis

Dear Shawn Wallwork

Thank you so much for the above mentioned graph chart. It would be so great sir, if you can include the workbook (hacked Tableau 8.3 Version) so that I can go through the calculation.

And also, Merry Christmas and Happy new year sir. You helped me so many times this year . Thank you so much for all your help. I am really  thankful to you

• ###### 22. Re: Box Whisker Plot along with Time of a Day in Y axis

Rajeev, you are welcome. It is a very tricky chart to make. I'll need to step you through it. But before I spend time doing that, please confirm that it is in fact the chart you are trying to produce. The circles represent the ending time of day of each job, for each day of week. The box & whisker is the standard Tableau default:

Please confirm that this is what you want. Thanks,

--Shawn

• ###### 23. Re: Box Whisker Plot along with Time of a Day in Y axis

This is exactly I am looking for . Your Y axis is giving the exact time and same is reflecting in the Tool tip as well.

Here the Green and Red is showing the status of the Job Name.

Could you please explain me the Process/steps that you followed while creating a map

• ###### 24. Re: Box Whisker Plot along with Time of a Day in Y axis

EDIT: As with all things in Tableau there are usually several different ways to accomplish the same outcome. The following turns out to be the 'hard' way; whereas KK's post below this is the 'easy' way. Because of this I considered taking this down altogether, but I think maybe there are a few techniques in here that might be useful in other situations. And it took a while to write up, so here it is anyway....

OK Rajeev, glad we're on the same page. First here's where we are going:

And here's how we get there....

1. Create two calculations:

Hour/Minute of End Time

DATETIME( MAKETIME( DATEPART('hour', [End time]), DATEPART('minute', [End time]), 00  ) )

Hour/Minute Float

FLOAT( MAKETIME( DATEPART('hour', [End time]), DATEPART('minute', [End time]), 00  ) )

2. Build the basic viz:

Put a discrete (blue) Weekday of [End time] on the column shelf, and a continuous (green) exact date of the [Hour/Minute of End Time] on the row shelf. You should get this:

Notice that the Y-axis date is 1/1/1900 (In yellow.) This is what we want. By doing this we eliminate the problem of each day of week having a different date. Because of how we created our Y-axis date we in effect eliminate (normalize) the individual dates from the viz and leave only the hours & minutes, allowing us to use an Exact continuous date, which is what we need. Why? The Exact keeps the marks from being rolled up to a coarser level of detail (hour in our case). The continuous is keeps T from dividing the viz into individual (discrete) rows. (Change it to Discrete to see what I mean.)

Let's clean things up a bit by changing Automatic to Circle type marks. And setting the size to something appropriate:

Also note that we can not use Box & Whisker references on a date/time axis.

So get get the Box & Whiskers to work, we'll need to change the date/time values to number values. That's what that second calculation was created to do. And then set up a dual axis chart. So first drag the Hour/Minute Float onto the Row shelf. You should get something similar to this:

Now let's put them together as a dual axis. Also we need to color and size the two different sets of marks so we can see what's going on. So first drag the two Measure Names fields Tableau assumed we wanted on the color shelf (sometime you are just too helpful T), and color/size accordingly:

This looks promising. The Add Reference Line option is now available, which is what we need to be able to add Box & Whisker reference lines. And it appears the marks can be made to line up by adjusting the sync between the two axis. However, that's going to be tricky since the Synchronize Axis option is grayed out.

4. Synchronize the axis:

To get them to sync, we will need to 'fix' both axis. First we will Edit the left [Hour/Minute of End Time] axis. When you first open the edit dialog box it will look like this:

You need to edit it to look like this:

I changed it to Fixed. Set the start/end to cover a single day 12:00 am to 11:59 pm. Changed the Title to 'Time of Day' and unchecked the Automatic subtitle (so the 1/1/1900 wouldn't appear in the title).

Now let's fix the other side to sync. We start with this:

And we want to end up with this:

Yep that's it, just change it to a fixed axis starting at zero and ending at one. (The extra decimals was just Tableau padding out the axis.) You can ignore the title as we will be hiding the axis anyway. So here's what you should have:

Notice how all the marks now line up. Doing this sort of fixed sync works on this viz because there are alway 24/60 minutes hours in a day. And the FLOAT() function just turns these Babylonian base 60 numbers into Arabic base 10 numbers (according to Jonathan). So it appears that 1 in the Arabic system = 1 day in the Babylonian system. Nice to know, thanks for that Jonathan.

5. Tooltips and final clean-up:

First we need to keep one set of marks and hide the other. In a dual axis the top most marks are always the marks of the pill farthest to the right, in this case the [Hour/Minute Float] pill. So let's turn the color of the other marks, the [Hour/Minute Float], down to 0% transparent. And the size of these set to the smallest possible. You'll get this:

And then the last things we need to clean up is the tooltip, hide the right axis and put [Status] on the color shelf. Here's the formula I used for the tooltip:

LEFT(

RIGHT(

STR([Hour/Minute of End Time]),

LEN(STR( [Hour/Minute of End Time] ))-

FIND( STR([Hour/Minute of End Time]), ' ' )

), 5)

That's it.

Let me know if you have questions. Also KK may have come up with an easier way of doing this. I'll be interested to see.

Cheers,

--Shawn

NOTE: i posted a 9.2 workbook, Rajeev I'll let you hack it back.

1 of 1 people found this helpful
• ###### 25. Re: Box Whisker Plot along with Time of a Day in Y axis

Rajeev:

I just saw another post from Shawn Wallwork on how to show HH:MM on the tooltip. He asked me to post my workbook and how I did managed to show the HH:MM in the tool tip. Here you go;

- Created a calc field "secs" to get seconds from the start time

(datepart('hour', [Start time]) * 3600) +

(datepart('minute', [Start time]) * 60)

- Using secs, I created another calc "Hours + Minutes" to get hours and minutes. Ex. 79200 secs = 2200 hours (military time)

- Use Hours + Minutes on the row shelf. Edit axis to show 0 to 2400

- Format the axis labels to show custom 00:00

See attached workbook...

Hope this helps.

..kk

1 of 1 people found this helpful
• ###### 26. Re: Box Whisker Plot along with Time of a Day in Y axis

Obviously I totally over-thought this one! Nice job KK.

Cheers,

--Shawn

• ###### 27. Re: Box Whisker Plot along with Time of a Day in Y axis

Rajeev, when using KK's solution, you'll need to put the [End Time] date field on the Detail shelf as a discrete (blue) pill, otherwise you will only get 72 of the 373 individual data points you have in this dataset. Other than that, KK's solution is much easier to accomplish. Does if work for you?

--Shawn

• ###### 28. Re: Box Whisker Plot along with Time of a Day in Y axis

Dear Shawn Wallwork

Thanks for taking so much pain for solving this issue .It wouldnt be possible without you. I am really thankful to you.

This is exactly what I wanted . But here I attached a sample data , I need to use the method which you described with my actual SQL Server data. I will confirm on Monday once I will go to office. Currently I cant access my office Environment. BUT I really appreciate all your help and Time Sir. Thanks once again

• ###### 29. Re: Box Whisker Plot along with Time of a Day in Y axis

Dear kk.molugu

Thanks Sir, Really appreciate your time and efforts. Its really very useful

This screenshot is specially for my version as I cant open Tableau version 9 file in my office

1 2 3