13 Replies Latest reply on Jul 23, 2014 3:03 AM by Jonathan Drummey

    How to filter a calendar without losing dates?

    Adam Markon

      I've currently built a calendar to measure how often systems on our servers are meeting their SLA requirements and the calendar works fine:

      SLACalendar.PNG

      The problem is that I'm also trying to include a filter that filters the calendar by system (i.e. If system A is selected, the calendar only reflects system A's SLA information), but when this filter is used, we lose all dates that don't have data, like this:

      SLACalendarFiltered.PNG

      Does anyone know how we can use this filter without losing all of those other days?

       

      Because I know someone will ask about the data, I work for a bank so I can't post any actual data. What I can tell you is that we have all of our data about each job joined through multiple tables, and then in order to get all of the dates into the query I did a right join with the table that holds the dates, which I have a feeling could be related to the problem.

       

      Any input would be helpful.

       

       

        • 1. Re: How to filter a calendar without losing dates?
          Matt Lutton

          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.

           

          A Jedi (Filter and Table Calc) Trick | Tableau Software

          • 2. Re: How to filter a calendar without losing dates?
            Adam Markon

            I've tried this, but I still get the same result.

            • 3. Re: How to filter a calendar without losing dates?
              Matt Lutton

              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.

              • 4. Re: How to filter a calendar without losing dates?
                Adam Markon

                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.

                • 5. Re: How to filter a calendar without losing dates?
                  Matt Lutton

                  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.

                  • 6. Re: How to filter a calendar without losing dates?
                    Noah Salvaterra

                    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.

                     

                    N.

                    • 7. Re: How to filter a calendar without losing dates?
                      Matt Lutton

                      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.

                      • 8. Re: How to filter a calendar without losing dates?
                        Jonathan Drummey

                        Hi Noah,

                         

                        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.

                         

                        Jonathan

                        • 9. Re: How to filter a calendar without losing dates?
                          kettan

                          I added a row for each date in June and July. Then everything should work as long as Null is included.

                          • 10. Re: How to filter a calendar without losing dates?
                            Adam Markon

                            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.

                            • 11. Re: How to filter a calendar without losing dates?
                              kettan

                              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
                              
                              
                              
                              
                              
                              
                              • 12. Re: How to filter a calendar without losing dates?
                                Michel Caissie

                                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.

                                 

                                 

                                select

                                d.Date as 'Date',

                                c.Dimension as 'Dimension',

                                null as 'OtherMeasure'

                                from

                                  (

                                  select whateverTable.dimension as 'Dimension' from whateverTable

                                  ) c

                                join

                                  (

                                  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

                                  ) H

                                   join

                                  ( 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

                                  ) T

                                   join

                                  ( 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

                                    ) U

                                where

                                  (date('2014-01-01') +interval (H+T+U) day) < '2016-01-01'

                                ) d

                                 

                                union all

                                 

                                select

                                whateverTable.date  as 'Date',

                                whateverTable.dimension  as 'Dimension',

                                whateverTable.measure  as 'Measure'

                                from whateverTable

                                 

                                 

                                Note that using this you don't need to rely on any table to provide the dates, they are generated and not queried

                                 

                                Michel

                                • 13. Re: How to filter a calendar without losing dates?
                                  Jonathan Drummey

                                  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.


                                  Jonathan

                                  1 of 1 people found this helpful