7 Replies Latest reply on Dec 28, 2016 6:34 AM by paolo.raia

    Filter by date period on two different dimensions

    paolo.raia

      Hi,

       

      I have two dimensions: Date File Expected and Date File Received.  I want to be able to count total instances of each dimension in any given period.

       

      For example, I have this:-

      I want to be able to produce daily counts filtered by any given date range.  So if the date range is 1-Jan to 6-Jan, I should get the following obvious result:-

      However, if I use, say, the Date File Expected date as the filter driver, I am assuming it may exclude the Date File Received instances relating to 5-Jan because this date is not included in the "date expected" set:-

      Do I need to create some form of independent date filter based on a mapped file?

       

      Any advice greatly received.

       

      Regards.

        • 1. Re: Filter by date period on two different dimensions
          Shinichiro Murakami

          Paolo,

           

          I may provide a solution, but need to see your data.

          Could you attach packaged workbook.

           

           

          Packaged workbooks: when, why, how

           

          Thanks,

          Shin

          • 2. Re: Filter by date period on two different dimensions
            paolo.raia

            Hi Shin,

             

            I have attached the workbook.

             

            Many thanks!

            • 3. Re: Filter by date period on two different dimensions
              Joshua Milligan

              Paolo,

               

              What is your data source?  There are several approaches to solving this kind of problem, but almost all are going to involve merging the date fields into a single field that you can use as a single dimension.  For example you might:

              1. Pivot the data on the two date fields such that you have a single column for date and another column indicating whether the date is expected or received
              2. Union the data (using Custom SQL or Tableau's union ability) to itself so that you have two rows for each unique file name so that you can use one row for the expected and one row for the received with a single date representing both.
              3. Use data blending with a master (scaffolding) data source that contains a field with every date and then blend to 1 copy of your data set above to get expected and another copy to get received.

               

              Generically, I would suggest them in the order listed above. But It all depends on your data source as to what is possible. 

               

              Hope that helps!

              Joshua

              • 4. Re: Filter by date period on two different dimensions
                Joshua Milligan

                Paolo,

                 

                With Excel, you can pivot the data:

                 

                 

                And it becomes this easy in Tableau:

                 

                 

                 

                (you can alias the values of [Expected or Received] to be a bit more friendly.

                 

                Hope that helps!

                Joshua

                • 5. Re: Filter by date period on two different dimensions
                  paolo.raia

                  Hi Joshua,

                   

                  Many thanks for your very informative feedback.

                   

                  The data source resides in one SQL table.  One option I did try was to create an Excel file with two columns: one listing all dates and a helper column full of 1's.  Then I created a calculated field in Tableau with just the number 1 and tried some sort of linking/blending, but to no avail.

                   

                  I could go down the road of just creating two separate dashboards for each dimension, but I'd rather find a solution to keeping this all in one visual.

                   

                  I'm interested to learn more about your options 2 and 3.  Could you expand a little?

                   

                  Many thanks again.

                   

                  Best regards.

                  • 6. Re: Filter by date period on two different dimensions
                    Joshua Milligan

                    Hi Paolo,

                     

                    Sure!  So, let's just consider option #2 for now, because #3 really is a last resort kind of thing that introduces a lot of complexity.

                     

                    Since you are using SQL Server, then you should be able to use a custom SQL statement to union together two copies of the data to give you what you need.  Tableau 10.2, in beta now, will allow you to union together data without using custom SQL, but for now, it the SQL script will look something like:

                     

                    SELECT [Unique File Name], [Date File Received] AS [Date], 'Received' AS [Expected or Received]
                    FROM Table
                    
                    UNION ALL
                    
                    SELECT [Unique File Name], [Date File Expected] AS [Date], 'Expected' as [Expected or Received]
                    FROM Table
                    

                     

                    This will give you a data set very similar to the one based on Excel above.  You'll have two rows per file with one date field and an indicator for which row represents received and which represents expected.  That will make it very easy to generate the desired view in Tableau.

                     

                    Hope that helps!

                    Joshua

                    • 7. Re: Filter by date period on two different dimensions
                      paolo.raia

                      Hi Joshua and my apologies for not replying sooner.

                       

                      I have not had the opportunity to try this as yet, but will do at some point very soon.

                       

                      Many thanks again for your feedback.

                       

                      Regards.