I'm looking at this. (BTW, you said it was a small workbook, and yes it is. But the data set is large. It took me a while to figure out your data, and a smaller set would have helped with that.)
Essentially for any Animal ID, you can have a few rows, but not many. So for ID 199081, for example, you have several rows, with the minimum [Source Date] being 1/28/17. And you have multiple [Final Outcomes], one per [Source Date], and the maximum [Final outcome] is 4/6/17.
If I am understanding your question, you want this ID counted on each date between 1/28/17 and 4/6/17. (Or maybe only on those dates between each [Source Date] and its associated [Final Outcome] without counting it in any gaps between those strings of dates.)
This is going to be difficult. Tableau does its evaluation based on dimensional marks. ID 199081 doesn't have a data source row for 2/1/17 (which is a date within the range of a Source and Final) so there is no mark that would trigger a count. To make that work you would need to force a row for every date (for every ID.)
There are convoluted ways to force table calcs to make marks on all dates along an axis (whether or not there is a row for that ID on that date), but even at that, you need some dimension that would create a mark on every date. We could pivot the three date columns into one, but I can imagine that there are days when no Source or Status or Final Outcome occurred for any ID, and therefore that date would not exist on your grid to generate a count.
1 of 1 people found this helpful
In the attached I created an excel data source from your original. I first made a calc to grab only IDs that had multiple Source Dates (see [Calculation1] in your original data source.) Then I could filter on that to get values greater than 1. This pared the over all data set to less than 10,000.
From there I did View Data, and cut-and-pasted it all into excel. (Attached.)
I made a new data source based on that file. And then I pivoted the three date fields so that I have one dimension that contains all the dates among the three columns. I put that onto Sheet 3, and sure enough, there are missing dates (Jan 8, 2018, for example). So we can't even use that as an axis along which to do calcs. (You won't get counts of anything on dates that don't exist on the axis.)
Apologies on the size of data, lesson for next time.
Yes you have essentially nailed it, that is exactly the quandary i am faced with.
So essentially i need to find data that shows the animals existence each day it was in care?
That would be the easy thing.
It also might result in a HUGE data source.
I'm looking at something else first.
2 of 2 people found this helpful
there is a technique called Data Scaffolding.
Here is a link about it (with other links within it).
This was written before the dawn of Tableau Prep. There might be an easier way to make it happen with Tableau Prep, but I'm not familiar enough with Prep yet to recommend that.
Thank you, I will watch the video.
2 of 2 people found this helpful
This is in agreement with Joe that a data scaffold would be helpful.
Using Prep, I joined Joe's sample set with a single column of consecutive dates.
This seemed to produce a running list of days between each pair of SourceDate-FinalOutcomeDate
(please see screenshot below).
Attached in the Forum Thread is the xlsx, tflx, and twbx (v10.3).
Here are some other general discussions of somewhat similar tasks:
did you fixed the issue?
If yes, please choose the correct answer or describe how you did to close the thread.
Thank you this got me squared away!