I'd love to help out, but I'm having a difficult time understanding your desired state.
Can you lay out your business case, how nulls should be handled, value from this chart, etc?
Thanks for the quick reply.
The data from the access database reflects when an issue/action is identified and when the action is completed
NULL in the End Date reflect that it's still an open issue/action
The chart, when broken out by category for example (not in the data), would help determine where additional training or communication is needed
Also, it will also help see if the training and communication was effective by seeing a drop in issues/actions
Does that make sense?
This is a real interesting problem for a couple of reasons - first you can't pivot the data which would be the first approach - second your calculation is not a typical running total
The first part - you need to have a framework to serve as the basis for your date calculations - a continuous m/d/y field that you then use a cross data base join to tie back to your In/Out data
Ok that sounds nasty **** it really is not all that difficult
- I added a sheet your excel that is nothing more than a list of dates from you earliest to the last date in your data set - I used an excel sheet you could probably create it using sql or something in your source data
Then you bring both sheets into Tableau and join them as shown below -
The join is created by opening the drop down on the 2 data sources and selection Edit Calculation - a box will open and input =1 then OK
Then go to the sheet 1 tab
Now create a series of calculations to count the pluses and minuses and then to do the running sums
First the Pluses if [Date]=[Start Date] then 1 else 0 end -- this does noting other than look ate the Data list the I added and joined to your data and put a value of 1 when = to the start date
The the minuses values if [Date]=[End Date] then -1 else 0 end
And then the difference as running totals RUNNING_SUM(sum([counter plus])) + RUNNING_SUM( sum([counter minus])) - this last calculation loos at the total running pluses and minuses separately then does the math
The results in table form are
In chart form
Let me know if this helps
This is great - thanks a lot
Unfortunately, I still have 10.0 (which doesn't have the Edit Join Calculation feature) so I can't test it out personally
Our company is getting 10.3 soon so I'm eager to try this new feature out
Marking as Correct Answer (unless you have a 10.0 solution )
That is disturbing - I will spend some time and try to find a workaround and let you know
I'm still looking but I ran across this
in the response Tom W (he is an ambassador and is really good) use sql to do something similar
I'm not a sql guy but it may help
I will continue looking
There is another resource kettan that you can try
He has a wealth of experience that includes data functions
He was the source for the original post I sent you and has experience that dates back past t-9
Method 1 WINDOW CALCULATIONS
Have you tried Showing Records That Fall Within a Period of Time | Tableau Software ?
The nice thing with this method is that it doesn't increase the number of underlying rows and (I think) neither need handling of null in end date.
OBS. I have never used this method and therefore without knowledge/experience as a helper in this regard.
Method 2 PERIOD SPLIT INTO ROWS
You could use the technique described in Split Periods Into Rows Dynamically .
Since handling of nulls isn't really included in that document, it is described here:
[Period] <= DATEDIFF( 'month', MAX([Cover Start Date],[Period Start]), MIN([Cover End Date],[Period End]) )
Filter (plus null handling):
[Period] <= DATEDIFF( 'month', MAX([Cover Start Date],[Period Start]), MIN(IFNULL([Cover End Date],[Period End]),[Period End]) )
This was a modification of the Filter formula mentioned under appendix at the bottom of mentioned document.
An Appendix B is hereby added to Split Periods Into Rows Dynamically so null handling also is mentioned.
Jim, I am very interested in your method here as I regularly work with schedule data. However I am working with 10.2.2 so while I can do cross data base join on calculations, I cannot open you packaged workbook. Is there any way you could give more detail on the join you do witht he created excel scaffold date sheet please? Thanks!
This thread is closed but please contact me at firstname.lastname@example.org and if you can send a sample of your data -
I would llike to see what you are doing and better understand your specific need