1 Reply Latest reply on Jun 3, 2016 1:29 PM by Frank Casella

    Filter date in visualization but not in calculation

    Frank Casella

      Hi,

       

      Still fairly new to Tableau, so would appreciate any help you can provide.  Unfortunately, my company firewall prevents me from uploading a copy of the workbook I am creating, so I'll do my best to describe my problem.

       

      I have a bubble chart (scatter plot) that has the current result on the X-Axis and the trend on the Y-Axis, showing state as the detail.

      Bubble Chart.png

      the X-axis is a table calculation using "Table (across)" and uses [Parameter].[Period] to vary the time period used for the calculation.:

       

      CASE [Parameter].[Period]

      WHEN 'Most Recent Month' THEN [Result (current month)]

      WHEN 'Year-to-date' THEN [Result (YTD)]

      WHEN 'Rolling 12 Months' THEN [Result (R12)]

      WHEN 'Prior Year-to-Date' THEN [Result (YOY)]

      END

       

      When I first created the chart, it would show a mark for each month used in the calculation.  This was fine for the most recent month, but messy the other calculations.  To get around this, I added a Date Filter table calculation:

       

      IF DATEADD('month',[Date Offset], LOOKUP(ATTR([Date]),0)) = [Parameter].[Data Month]

      // the date offset is to account for some measures which take 6 or 12 months to mature

      THEN 1

      END

       

      Unfortunately, the data filter is also affecting the X-Axis calculation. As a result, the year-to-date and rolling 12 months show the most recent month only and the prior year-to-date shows nothing.

       

      How do I get the X-Axis calculations to ignore the date filter?

       

      Here's what the Rolling 12 Month equation looks like, in case the way I'm calculating the measures is the issue...

       

      [Result (R12)]

      WINDOW_SUM(SUM([Numerator])*[Multiplier (R12)])/

      WINDOW_SUM(SUM([Denominator])*[Multiplier (R12)])

       

      [Multiplier (R12)]

      IF DATEDIFF('month',ATTR([Date]),[Parameter].[Data Month]) < [Date Offset] + 12

      AND DATEDIFF('month',ATTR([Date]),[Parameter].[Data Month]) >= [Date Offset]

      THEN 1

      ELSE 0

      END

       

      Thank you for any assistance you can provide.

       

      -Frank