1 Reply Latest reply on Sep 26, 2012 12:07 PM by dhveryoldaccount

    Filter on calculated field and parameter selection?

      I'm having trouble filtering a calculated field. Here's the calculation.

       

      if month([Date]) = [Month #] then "MTD"

      elseif month([Date]) <= [Month #] then "YTD"

      end

       

      [Month #] is a user parameter that's the integer value for each month, January = 1, February = 2, etc.

       

      When I try to use the calculated field as a filter I get some weirdness. As an example, lets say a user selects "9" from the [Month #] parameter. Also, let's say the user selects "YTD" from the quick filter.

       

      What appears on the worksheet are not all months, 1 though 9, but all months 1 through 8. I think this is because the filter/calculated field says, "When month([Date]) is the same as [Month #], [Month #] can only be "MTD" even if "YTD" is selected".

       

      Any ideas? I've tried a couple other ways unsuccessfully so I'm open to changing things around if needed.

       

      Thanks!

        • 1. Re: Filter on calculated field and parameter selection?

          Okay, so I figured it out. Here's what I did.

           

          I created 2 new calculated fields, "MTD" and "YTD". Below are the contents.

           

          "MTD"

          if month([Date]) = [Month #] then [Date] end

           

          "YTD"

          if month([Date]) <= [Month #] then [Date] end

           

          And a 3rd calculation called "View By" which looks like this.

          case [Sum by]

          when "YTD" then [YTD]

          when "MTD" then [MTD]

          end

           

          "View By" is then dragged to the filters shelf. With the same worksheet I now have 2 user parameters, [Month #] and [Sum by]. Now the parameters have full control over the filter!

           

          Users can select the month they'd like to view and what kind of aggregation they'd like. I needed to make this dead simple because most, if not all, users will be unfamiliar with Tableau.

           

          Hope this helps anyone with similar issues.