I was actually going to suggest Option 1: join to a master date table on the criteria you've mentioned.
[Master Date] > [A]
[Master Date] < [B]
Would you be able to share a sample workbook that mimics your work?
Alternatively, how are they selecting the day to view? If just using a date picker parameter, then you can write the filter to get the appropriate rows as well.
The master date table option may be worth exploring. My data is in SQL and I'm an amateur in basically all aspects of databases, so I get a bit intimidated by table creation and modification. I've got a contractor I can consult for assistance on that though. I will look into it.
Also, on further investigation of my problem with Option 2, I think I've found the problem. The issue is that date [B] isn't always populated, because if a case is still 'pending' in the middle phase the completion date hasn't been entered. So, a proper calculation of this work phase is when, on DATE, DATE was between [A] and [B], OR DATE was after [A] and [B] remains NULL. I think Option 2 from the link only does the former calculation and misses the latter.
Will see what I can do on a sample workbook. Will take some effort to scrub my data as it is loaded with PII. For the date being selected, it would be a range. What is desired is to see changes in inventory over time, likely in the 1-2 year range mark. So, for example, I would want to display the historical inventory from 1/1/18 to the present.
Gotcha. I think. Heh.
Yeah, often times you can make a pseudo date placeholder that's just like IFNULL([B Date], TODAY()) to use as you only care about data through today anyway (or COALESCE([B], CAST(GETDATE() as date) in SQL).
You could probably do this in SQL pretty easily before bringing into Tableau then.
The tricky thing I didn't mention, however, is you probably want this as a left join, as you may want to include dates that don't have any records that were open on a day.
Record 123, 1/1/2019, 1/5/2019
Record 456, 1/7/2019, 1/16/2019
Just joining on the logic I mentioned originally would not have 1/6/2019 in your data, so you may want to do it in SQL as a left join. OR you could look at using a date table as your primary data source and do some blending in Tableau, but I don't do that very often admittedly.
1 of 1 people found this helpful
I got the master date table added and then went to try Option 1 and it worked perfectly and much, much simpler than Option 2. Thanks for your help!
Excellent! Glad it worked out for you! I had to do the same thing looking at ER Room Utilization per Hour - so definitely was an interesting challenge that you'll encounter again! Heh.