HI Joe Oppelt
I kicked this of with a "Scaffold"-dataset with all individual dates to support the visualization on "missing" in between dates but not able to get them "populated" as requested. Could you bring some "light" in the day?
Example solution attached (Tableau 10.3).
Steps to produce:
1. Pivot the "Create Date", "Basic Finish Date", and "CLOSE Date" fields and rename the resulting pivot fields "Date Type" and "Date".
2. Create a calculated field [Date calc]. In my example, this does 2 thing: (1) turns the "Date" string into a date field (because I'm on US locale so Tableau didn't already recognize the European date format); and (2) add 1 day to the "Basic Finish Date" dates to reflect that the service order should start to show late the day after the finish date.
Now the real fun begins. Please note carefully the default table calculations that were configured for each calculation below.
3. Create a calculated field [Late Status Change] to identify changes in late status, based on [Date calc]. "Basic Finish Date" (being the day after the finish date) adds a 1 to the status, and "CLOSE Date" subtracts 1. "Created Date" doesn't do anything.
4. Create a calculated field [Late clamped] which is the running total of the above [Late Status Change], but clamped to ensure it always falls between 0 and 1. (A service order that is closed before its target finish date would push the running total into -1, which we don't want.)
5. Create a calculated field [Late accumulation] which is a running total of [Late clamped]. This is how we accumulate delay days for every day that the service order is late. Note that the running total is multiplied against the current value of [Late clamped] — once it becomes zero (not late) then the running total of late days collapses to zero.
6. The calculations so far have been computed for each individual Service Order Number (soon we'll see how that's dictated in the view). Create a calculated field [TOTAL LATE DAYS] which is simply the sum of the accumulated late days across all service orders. Note the default able calculation differs from the previous calculations.
Now to construct your view…
7. Place [Date calc] on the COLUMNS shelf. Set it to a Measure (DAY) and turn on "Show Missing Values". This will create the appropriate scaffolding for the axis.
8. Please [TOTAL LATE DAYS] on the ROWS shelf. It will initially be red, because we are missing a level of detail (Service Order Number) required for the intermediate table calcs.
9. Add [Service Order Number] to the Marks shelf as a Details element, and your chart should look very much like what you modeled in Excel.
You will note that this chart has one drawback: during periods when there are no open service orders, it displays a gap in the line, rather than an ongoing zero. I tried a few things, but could not get it to render marks in that part of the chart. Someone else may be able to easily spot where to insert an appropriate ZN() or IFNULL() into the calculations to fix this. Or, it may require more robust scaffolding, such as what Norbert was building out. (I tried to stick to a solution that does not require a second table for scaffolding.)
Still, I hope this helps get you close to what you want to report. See the "Illustration of Calcs" tab on the workbook to see how the intermediate calculations come together to form the final solution.
Delay-days-table-calcs-10.3.twbx 29.3 KB
Thanks for coming to the table.
I can't get "my head" around it but there should be a way out based on your calculations in combination with scaffolding.
Find my "trial" in attached workbook version 10.1
first of all thank you for the answer but especially thank you for the long and detailed information you provided to me.
I'm going to try your solution as soon as possible and I'll be back to you.
In my database I've already created a Pivot, I've just to figure out how can I "save" my graphs creating another pivot.
By the way as I said, thank you very much! To both of you!
I tried and I think that we are close to the solution but not yet arrived.
I'll be honest, it's not crystal clear to me al the process because I'm not so expert in tableu (I started using it 2 months ago) and I have some difficulties to understand the table calculations so it's quite hard to me to personalize what you wrote.
In any case, I think that the issue is referred to the Service Orders in Open status (so the ones that do not have the 'Closing DATE' filled in).
On these Service Orders the delay should be increase if the basic finish date is lower than the last data refresh because I asked to close a SO in date in the past and this is not closed yet so it's in late.
I think that my example could be better that that because there was just one SO in Open Status so the graph is the same.
In my big database the impact is bigger.
By the way I'll try to find a way and I think that it's time to understand what these table calculations are
I changed the database to make it easier to test.
You can find it in the excel file "Example1.xlsx" attached where there is also the graph. I'm sorry if the formula are not the best but I need the result fast so I didn't thought too much.
With the new database, this is what I have in Excel:
This is what I have in Tableau:
I'm almost sure that the differences depends on the not yet closed SO.
1 of 1 people found this helpful
Yes, the open orders throw it off. Fortunately, the solution is very simple.
1. Change [Data calc] thus:
IFNULL([Data],TODAY()) + IF [Data type]='Basic Finish Date' THEN 1 ELSE 0 END
This will ensure that if there is no CLOSE date, today's date will be set. (Will ensure that the timeline axis extends all the way to today, which is nice.)
2. Change [Late status change] thus:
IF ISNULL([Data]) THEN 0 ELSE
CASE [Data type]
when 'Created Date' THEN 0
when 'Basic Finish Date' THEN 1
when 'Closing DATE' THEN -1
This will make sure the status change ignores the fake dates that we injected when CLOSE date is empty.
Revised workbook attached. And here's what it looks like:
Thank you again.
I just correct the "Data calc" because the "Basic finish date" in the future are not good to show:
IFNULL([Date],TODAY()) + IF [Date Type]='Basic Finish Date' THEN 1 ELSE 0 END
Thank you again.
Now I have to understand in detail, why you did this and how to apply to other cases (for example the evolution of number of service order (not the delay)!
I'm glad it's working for you! And I'm glad to hear that you have more of an understanding of Tableau's table calculations. They are a powerful feature that can be used for some really cool advanced behaviors, so mastering them will open up a world of possibilities for you.
Your "delay days" problem is actually the difficult part. Number of open service orders by day will be easy in comparison. What I posted here was actually an extension of a solution I recently wrote for "number of (active) unique records". See my post and example workbook over here, and use it as an exercise to build a solution for your data set. Re: Distinct Head Counts Between 2 Dates without Running Totals (V.10)