If you "show missing values" or "show empty rows" you will not get the location for the missing days, only the missing days?
Yes, because these data even not exist in the data source, so function like isnull() or ifnull() doesn't work either.
Unless you have a calendar date data set and you join your data to the calendar data set, it may work.
Is there a way I can do it as a SQL custom query or any other way?
Sure there is.
Here is a cross join solution with Tableau's built-in join feature, that is, no SQL custom query needed!
The technique used is in principal method 2 (Periods Intersecting Calendar Dates) from The Cross Join Collection.
Step 1: Create a sheet named Lookup (or whatever) with a column with minimum as many rows as the longest period. I used 31 rows (with numbers from 0 to 30). You just add more as needed. And of course, you could also use fewer
Step 2: Cross join (1=1) Data table with Lookup table.
Step 3: Create a calculated field that ensures that one row is returned per day in period and make it a filter.
[Day] <= DATEDIFF( 'day', [Location In], [Location Out] )
Step 4: Create a calculated field that shows the date of each day in period.
DATE(DATEADD( 'day', [Day], [Location In] ))
Step 5: Build view.
This first examples was made to make it easier to understand what goes on.
This latter example is an imitation of your question's expected output.
PS. This is a very common question as documented in FAQ: Open & Close Dates and even the third I have answered myself in the last two weeks. I like to say that this has become much easier with the new ability to cross join inside Tableau.
If Tableau featured Support non-equi-join criteria for new Excel & text connector then I would have put the "Filter" into the JOIN dialog (which at the same time would make the 1=1 join unnecessary):
DATA LOOKUP DATEDIFF( 'day', [Location In], [Location Out] ) <= [Day]
I am not sure, but if you are connected to a database, I think you might be able to do this directly in the join.
Attached Workbook Version: 10.3
This works perfectly! Thanks to everyone for their help.
You helped me with this problem recently. it works, however sometimes in the real data the products last in the locations for 100 of days, so doing a 1 x 1 cross join is impractical, it results in 100's of millions of rows! Is there any way of doing by having a look up of dates and doing a join formula where the dates are shown when they are >= location in and <= location out?
doing a 1 x 1 cross join is impractical, it results in 100's of millions of rows
Ja, that is not practical
ALTERNATIVE 1: NO INCREASE IN ROWS WHATSOEVER
I haven't used it and thus no expert, but this might be a recipe that might be used:
ALTERNATIVE 2: FEWER ROWS
Is there any way of doing by having a look up of dates and doing a join formula where the dates are shown when they are >= location in and <= location out?
Yes it is possible to integrate start and end dates of parameter filters into the data split of rows so no more rows are generated than those needed for the time window displayed. I might share an example of this later.
1 of 1 people found this helpful
Just saw this thread, there's an addition to @kettan's solution that in modern RDBMS's should result in the same query plan as a non-equi-join.
1) Create a calculated field with the desired criteria e.g. [Location In] >= [padded date] and [Location Out] <= [padded date].
2) Add that field as a data source filter. As a DS filter the calc will be added to the WHERE clause of every query.