5 Replies Latest reply on Sep 26, 2012 8:19 AM by katy.kasmai-nazeran

    Creating Range of Dates filter based on Start and End years from two fields

    katy.kasmai-nazeran

      Hello,

       

      I have the following Tableau project documenting Genocide activity: http://public.tableausoftware.com/views/GenocideInTime/Dashboard1?:embed=y

       

      I have two date Columns for each Row of Genocide, a From date (the year the genocide activity started) and a To date (the year the genocide activity ended).  Based on these two fields I want to create a Range of Date filter where the end-user could set a date range and see the Genocide activity in any given range.  Right now I have it set up with two different Filters for each field, but I'd like to combine it into one.

       

      Any ideas how to proceed?

       

       

      Thanks very much!

      http://public.tableausoftware.com/static/images/Ge/GenocideInTime/Dashboard1/1.png

        • 1. Re: Creating Range of Dates filter based on Start and End years from two fields
          Wilson Po

          Hi Katy,

           

          Combining the filter ranges for this dashboard is possible with a bit of data reshaping, but the design might be misleading.  As your data starts off with ranges of dates rather than data points for specific points in time, the challenge with this data set is how we represent the deaths when the user defined range is smaller than the range that our data provides.  For example, for the 3.5M Deaths in Cambodia between 4/1/1975-1/1/1979, if the user makes a selection for just the year of 1976, what would we like to display?  Would be still display the 3.5M deaths (which would yield some incorrect assumptions) or would we display 933K Deaths (deaths averaged out for within that year)?  While this does not change how we can bring both filters together, it should be considered as changing the interactions on your view can change the scope of the analysis.

           

          To combine this, we need to generate a date table that will range between the minimum start date and maximum end dates and use this new date as a generic date that will represent both start and end dates. I would just encourage building this a separate tab in Excel.  Next, it is about joining our data to this new table, use the following join clause when you connect via the multiple table connection.

          [Date from Date Table]>=[From]

          [Date from Date Table]<[To]

           

          Note that this will replicate your data for each date that the range falls within.  Since 45 months exists within the Range for Cambodia, it will be represented now as 45 distinct rows.  This in turn allows us to use 1 filter.

           

          The next apart is really then deciding how to handle the duplicates.  You either display using:

          (1) Maximum, Average or Minimum aggregation so additional rows of data does not multiply the values.

          (2) Create your own average calculation:

          [Killed]/Datediff('month',[From],[To])

           

          I've attached the workbook with this new data model. You can edit the data connection to see what was done is what is described from above.

           

          Hope this helps!

           

          Wilson

          1 of 1 people found this helpful
          • 2. Re: Creating Range of Dates filter based on Start and End years from two fields
            katy.kasmai-nazeran

            Hi Wilson!

             

            Thanks so much for your thorough response.  I think I was able to successfully follow your instructions; although I wasn't able to view your attached file on the Public Tableau since it says I can only open files that are published on the web end.

             

            Anyway, here is what I have: http://public.tableausoftware.com/views/GenocideInTime-TEST/Dashboard1?:embed=y

            If you could please confirm that I have successfully executed I would be very grateful.

             

            I do have two tweaks to the out put that I need to address:

             

            1. Even though I specify YEAR for the filter range and it shows it as such on my desktop version of the Tableau software, once I publish it and view it online it insists to show the full date with month/day/year 00:00 am/pm.  Am I missing something?  I only want to show the year.

             

            and

             

            2. If in a given time frame a country experienced more than one incident of genocide it only shows the Killed number for the most recent occurrence of genocide.  Any way to get it to show the Total Killed for that country for the specified time range? 

             

            Thanks again!

            Katy

            http://public.tableausoftware.com/static/images/Ge/GenocideInTime-TEST/Dashboard1/1.png

            • 3. Re: Creating Range of Dates filter based on Start and End years from two fields
              Wilson Po

              Hi Katy

               

              From the look of things, you did things correctly.  I did not realize you were working with just Tableau Public, but hopefully that workbook will be helpful for other folks. 

               

              As for your two other questions:

              1) I downloaded the workbook and found it odd that the publishing process is stripping off the Year() roll up. I can't say why this might be happening, but did you download your copy of Tableau Public from a while back?  It could be a bug that have been fixed already.  If not, I would contact Tableau support just to inform them that this is happening.  This type of feedback is invaluable for making the products better

              2) The min and max aggregation are limiting when incidents occur in the time range, They only capture the bigger or smaller value, rather than truly rolling up the right values.  I would encourage using the avg estimation I suggested earlier: [Killed]/Datediff('month',[From],[To]).  While it only estimates the deaths by finding the avg deaths/month per incident, it does roll up multiple incidents correctly.  Likewise, it does not overstate deaths for a time range as it estimates the deaths based on the months that have passed for the incidents.  If there is a Incident identifier (like a incident name or some field for use to distinguish distinct incidents),a table calc can add up the death of the incidents without the estimates, but it would be a bit more challenging to put together.

              1 of 1 people found this helpful
              • 4. Re: Creating Range of Dates filter based on Start and End years from two fields
                katy.kasmai-nazeran

                Hi Wilson,

                 

                Thanks again for your response!

                 

                I downloaded Tableau Public for the first time a few days ago, so I will go ahead and contact their support to see if they can fix that bug.

                 

                I tried your suggestion of the avg estimation calculation in the 'Add Table Calculation' under Color: Killed in Marks box - I couldn't find anywhere else to enter it.  This however gave me an error:

                 

                [Killed]/Datediff('month',[ByYear$_From],[ByYear$_To])

                *Error, The formula must be a valid table calculation.

                (I also tried 'year' instead of 'month'.)

                 

                I get the same error if I do MIN([Killed] ), but it says its a valid formula if I do something arbitrary like MIN([Killed] ) /TOTAL(MIN([Killed] ) )  .

                 

                Am I entering this in the wrong place or just wrong all together?

                • 5. Re: Creating Range of Dates filter based on Start and End years from two fields
                  katy.kasmai-nazeran

                  Hi,

                  I am still trying to get a response to my last question.  If anyone can help me out I would really appreciate it.

                   

                  Thanks!