I took a look at your workbook and have a 'sort of' answer.
It's sort of tricky what you're asking Tableau to do. Firstly, I took your Time to ship minutes and changed the calculation to a datediff() for simplicity. This returns the same number:
datediff('minute',[Order Date],[Ship Date])
The result in either instance is a numeric value. For each record, we return a scalar value for the difference between order date and ship date.
To get from this scalar value to a prettier version, you did some string transformation to get to your dd:hh:mm format. This is a string value that is unique for each row. You can't (as you well know) average a string value, what's the average of apple and orange? Well, there isn't one.
I tried looking at Reference line>format>numbers>custom, to put in dd:hh:mm which Tableau recognizes. However, it is translating an averaged value (2880 for example) into a dd:hh:mm format, which it attempts to turn into a single date, not date difference value. Unfortunately, this question has been asked a few times. I worked through this issue a few weeks back when trying to change a reference line for a big deliverable to not display as "sum of sum of avg goal line goal line" to something a little simpler.
Sorry I couldn't be of more assistance, I don't believe it to be possible at this time.
1 of 1 people found this helpful
I tried this a different way and got the result I believe you were looking for.
1. I created a second calculation to label [Time to ship minutes] with the formula:
STR(INT(AVG([Time to Ship Minutes])/1440))
+ "d:" +
STR(INT(AVG([Time to Ship Minutes])/60%24))
+ "h:" +
STR(INT(AVG([Time to Ship Minutes])%60))
2. I moved SUM([Expected time to ship Minutes]) to Columns, right clicked>dual axis
3. Right click on the dual axis, and 'Synchronize axis'
4. Remove reference line
5. Drag your new calculation from step 1 to the label for SUM([Expected time to ship Minutes])
6. You can then filter out your pane bands
7. I might recommend making the expected time to ship more transparent
I'm a little confused by your viz. If you are trying to show how long it takes to process an order vs. the expected time to process an order, and your expected time for all orders is 2 days (or 2880 minutes), then your reference line should be the same for each order and not moving.
If my understading is correct, then I think you need to change the formula in your calculated field called "Expected time to ship_DDHHMM), to look like this:
(and you should probably change the name to be Actual time to ship_DDHHMM)
Then, you need to change the aggregation on the Expected time to ship Minutes field to AVG:
Then change your refernce line to also use average:
Then you viz should look like this:
This doesn't address your issue of the tooltip for the reference line.
Using stacked/dual axis is a great work around. I never thought of it. Very clever!
However, we have to think about this, as our users are used to seeing "stacked" (dual axis) in a different context. Your solution is very practical. I will play with it and see if I could get it to where we want.. Thanks again for a nice workaround!
Thanks Kaz, In my production scenario, my reference line is pulled from another column and its not a constant. Though it might appear as constant in the Superstore sample I attached.
Glad to help, Karthik. If you feel my answer correctly solved your problem, please 'Mark as correct answer' so other users with similar problems can find this solution in the future.
Thank you Galen, Though yours is a nice workaround, I would like to keep the thread open for any possible answers that are closer to my requirement. Lets see.
The ability to edit reference line text is not a current functionality in Tableau as described here:
If you'd like to upvote the feature, vote here:
If you're looking for other workarounds, let me know what other options you'd like to explore
Thanks much Galen!!
I took your suggestions and changed the formatting a bit, to get it close to where I wanted. Its not quite 100%, but lets say 97%. We can definitely tell the users to keep the teal bar within the light gray one Yours looked like a stacked bar, hence I had some reservations yesterday. But changing the size of these 2 bars, and changing transparency got it closer to what we wanted. I have also up voted the Idea you have posted in here for the reference line tool tip. Thanks again for your great effort! I am marking it answered.