1 Reply Latest reply on Sep 8, 2016 5:10 PM by Patrick A Van Der Hyde

    Filter data using different date options (12/1 or 12/31) on same dashboard

    Ashley Dahlen

      Hello,

       

      I have data that is already pre calculated in a different system and we want to use Tableau Dashboards to visualize the data.

      We do not need Tableau to calculate anything for us therefore I am running into some problems.

       

      Data used for this example: electricity, natural gas, water and solid waste usage data comparing to corporate sustainability goals.

       

      - I had to include our YTD calculations (% needed to reduce to reach baseline... etc) in a row which also includes non calculated data like the 2007 baseline, as 12/31/2015 and 12/31/2016. This data will only be used to compare 2015 YTD to 2016 YTD and YTD data to the 2020 baseline etc.

      - I have the monthly data as 1/1/2016, 2/1/2016.. and so on.

       

      To show the correct YTD data I have to exclude the day 12/1/2015 and 12/1/2016 to ensure the monthly data is not included for the YTD portion.

       

      *** I need to have filter on the dashboard where the user can choose between YTD data or monthly data ----- filter that will have to pull the YTD data and exclude the day 12/1; and pull monthly data and exclude 12/31 and have this named so users understand

       

      Another issue I am running into with dates  - -

      Our "Sustainability year" is October 2014-September 2015 and October 2015 - September 2016 - - Users should be able to pick calendar year, or sustainability year when viewing monthly data to sort and filter how they selected....

      I tried to do this with the fiscal year option but making tableau not call it "FY" became too challenging for me...?

        • 1. Re: Filter data using different date options (12/1 or 12/31) on same dashboard
          Patrick A Van Der Hyde

          Hello Ashley Dahlen,

           

          Welcome to the forums.  So here's a solution for the first issue - the monthly versus YTD incusion of values:

          1.  Create a Parameter named [Year to Date or Monthly?]  with two values  - YTD and Monthly.

          2. then create this calculated field:

          ---

          case [Year to Date or Monthly?]

           

          When "YTD"

          Then if Datepart('day',[Date])=31 then [Date] END

           

          When "Monthly"

          Then if Datepart('day',[Date])=1 then [Date] END

           

          END

          ---

           

          You can swap out the Date as the  resulting field from the end of each if/then statement with a measure if you want instead.  I just use Date in the attached v10 workbook to show the values appearing/disappearing as changes are made to the parameter.

           

           

          For the Fiscal year/Sustainability year - do you need numbers to roll up to the Oct-Sept timelines as they do with Fiscal years or is this just for showing some break on reports while maintaining the Fiscal year starting in Jan?

           

          Patrick