You can create a Table Calc filter, which will be computed after most everything else, so you can effectively "filter" without losing underlying values. In the example below, the Sub-Category dimension is used--in yours, you'd be using the System field.
I've tried this, but I still get the same result.
OK. If you can mock up an example workbook using sample data, I'll be happy to take a look. Its impossible to know where you went wrong without seeing what you've done--but based on your description, a LOOKUP on your System field should work.
Sorry for the delay on this. I've recreated the calendar with sample data and its been attached to the original post. Thank you for all of your help.
This is a tricky one, and I was unable to find a way to make it work. I'm interested to see what others can come up with.
The attached sample demonstrates one potential workaround using a scaffold data source. This could be created from your database as a full outer join of the table with itself, though the performance implications of such a join could be significant (depending on the volume of data and your particular setup).
My initial reaction was that this should be solvable using a self blend, filtering the secondary source on Source Sys Name but not including it as part of the blend. I'm not sure why that didn't work... I'm guessing Jonathan Drummey would know. He may even have a better solution.
CalendarSampleNS.twbx 190.9 KB
I'm not clear on why that didn't work, either.... I also tried making the date and Source Sys Name Table Calcs, but that didn't work either.
Thanks for pinging me - I've got an explanation about why the self-blend didn't work (it has to do with order of operations), but it's longer than I've got time for right now, I'll write something up tomorrow. I think the scaffold is the way to go.
I had thought about that, but the real project doesn't get data from a spreadsheet, the data comes from a live connection to an oracle server, and the field [Cal Date] is in a separate table which has to be joined. Adding an extra row with null values would have the same result after the join is executed.
You can do it dynamically with UNION. All dates from the united table will be in the returned dataset.
I don't have access to Oracle at the moment and cannot test if the sample query below works.
That said, I think something like this would work (choose a small but big enough table):
SELECT [Cal Date], <and other columns> FROM Table_A a INNER JOIN Table_B b ON a.FK = b.PK <and other joins> UNION ALL SELECT TO_DATE('01-06-2014','DD-MM-YYYY') + ROWNUM - 1 AS [Cal Date] , NULL, NULL, NULL, <etc for all columns> FROM whatever_small_but_big_enough_table WHERE ROWNUM <= 61
I don't know for Oracle but using mySql, here is what i add (union all) to my Query to generate one row per day per field. The bunch of join will generate, in this case 999 rows, and the cast as date function will assign a date for each row, starting in this case from '2014-01-01' and incrementing by interval of one day.
The where clause determine the boundaries of the padding.
If more than 999 days are needed , then a stage for the thousands must be added.
d.Date as 'Date',
c.Dimension as 'Dimension',
null as 'OtherMeasure'
select whateverTable.dimension as 'Dimension' from whateverTable
select cast(date('2014-01-01') +interval (H+T+U) day as date) as 'Date'
from ( select 0 H union all
select 100 H union all
select 200 H union all
select 300 H union all
select 400 H union all
select 500 H union all
select 600 H union all
select 700 H union all
select 800 H union all
select 900 H
( select 0 T union all
select 10 T union all
select 20 T union all
select 30 T union all
select 40 T union all
select 50 T union all
select 60 T union all
select 70 T union all
select 80 T union all
select 90 T
( select 0 U union all
select 1 U union all
select 2 U union all
select 3 U union all
select 4 U union all
select 5 U union all
select 6 U union all
select 7 U union all
select 8 U union all
select 9 U
(date('2014-01-01') +interval (H+T+U) day) < '2016-01-01'
whateverTable.date as 'Date',
whateverTable.dimension as 'Dimension',
whateverTable.measure as 'Measure'
Note that using this you don't need to rely on any table to provide the dates, they are generated and not queried
1 of 1 people found this helpful
I'm staring at my notes for my Tableau Conference session on Extreme Data blending [plug alert] http://tcc14.tableauconference.com/schedule/wednesday#session-1045) [/plug alert] and wondering how much to try to type in here.
There's a natural assumption that if we filter on an aggregate measure like SUM(Sales) (whether from the primary source or secondary) that the filter will remove values of associated dimension(s) from the view. When it comes to using dimension filters from secondary sources (that were introduced in Tableau v8), though, I think we tend towards an assumption that a filter on the primary affects only the primary source and a filter on the secondary affects only the secondary source, and that isn't so, dimension filters work just like aggregate measure filters in this case.
Noah, awhile back you wrote in a forums thread that when working with data blending granularity is always "staring us in the face," which is a brilliant, pithy summation. The way I've come to shorthand the filtering question is "Filters affect the entire view."*** Another way to think about it is that the domain of values we see for a filter is created at one point in Tableau's pipeline and the filter is applied at another point in the pipeline, in this case for a regular dimension filter the filter is applied on the "integrated", blended data.
Here's an example from this data: In the original data for the date of June 27 there's only one value for Source Sys Name, System 7. So if we filter out System 7 in June, we're not going to see June 27. In the self-data blend scenario with the original data source, if the blend is only on the Date and the secondary is filtered on Source Sys Name, when System 7 is removed in June then there is no June 27 in the secondary source, which means theres no June 27 in the blended data set (since the blend is on Date) and that disappears. We can see the same thing in the scaffold source if we turn off the blend on Source Sys Name. Even though there is a record for June 27 w/a Null Source Sys Name in the primary, removing System 7 from the secondary means there's no longer a June 27 in the secondary source, which means there's no June 27 in the blended data set (since the blend is on Date) and that disappears.
***There's a whole deeper explanation (with graphics!) in my TC presentation on how Tableau figures out what gets blended and what doesn't, and how there are cases where we can actually filter one source but not another.