3 Replies Latest reply on Jun 27, 2013 6:18 PM by David Andrade

    How to include ALL dates, but limit user to only view N periods on time series chart?

    David Andrade

      I have a time series chart. It shows something like CPA over time. I have another line on the dual axis that shows the CPA moving average. The number of periods is based on a parameter that dynamically controls how many periods to average.

       

      All well and good so far...

       

      But, I want to have a date parameter that allows the user to click on a certain date. When the user clicks on that date, they'll only see the last N periods of data to look at on the chart ... WITHOUT filtering on the date range and messing up the moving average for the earliest date they'll see on the chart.

       

      So, no true filter of dates on the chart and user is limited to view their selected date + N previous periods on the chart.

       

      Catch my drift? Can Tableau do this?

       

      -dave

        • 1. Re: How to include ALL dates, but limit user to only view N periods on time series chart?
          Alex Kerin

          Yes, it's kind of a hack - you use a special filter so that the filtering is done in Tableau after the moving average is calculated using a date filter calc of: lookup(min([Date]),0) The partitioning of this needs to be set to compute using  [Date], not Table Across (right click the pill while on the filter, compute using). See the attached and note how the moving av does not change after you filter the second sheet.

          3 of 3 people found this helpful
          • 2. Re: How to include ALL dates, but limit user to only view N periods on time series chart?
            David Andrade

            Thanks Alex, this is helpful. It shows me how to solve one of the 2 problems I had - how to get the correct 3 week moving average when, on my sheet, I've filtered off the last 1 of 3 weeks. Wicked smaht.

             

            Still stuck on the "tunnel view of the data" part. The best I can come up with is producing two new date calcs, one for the current date and one for the current date minus 2 weeks, then use reference lines to shade beyond the "current date" line and below the "current date minus 2 weeks" line.

             

            The real fix I'm thinking of would prevent the user from even seeing what's to the right and left of those reference lines, zooming in on the 3 week range of data created by selecting a specific date through the date parameter and cutting off all data more than 2 weeks prior to that date. The key factor here is that the user will select a single date using a compact list with a discrete date in my filter and get this result. And yes, I know I can make it continuous with a range of dates and just slide the bar to get what I want, but the client wants the end-user to select only 1 pre-determined date from a drop down list to make this happen.

             

            Anyone have any more suggestions to solve my moving average / date filter dilemma?

            • 3. Re: How to include ALL dates, but limit user to only view N periods on time series chart?
              David Andrade

              Hey ya'll I figured it out. It was as simple as creating a new boolean calc and adding that calc to my filters section where the only acceptable value is 1.

               

              if date([Date]) <= [Date Current] and date([Date]) >= [Date Minus 2 Weeks]

              then 1 else 0 end

               

              To get the [Date Minus 2 Weeks] calc:

              DATEADD('week',-2,[Date Parameter])

               

              Not sure how much more I can share on here... but if you have any further questions let me know!