3 Replies Latest reply on Nov 22, 2016 10:57 AM by Joe Oppelt

    Parameters and Metrics

    Hugo Portefaix

      Hi,

       

      I'm using parameters to have my users select the metrics they want to appear in a dashboard.

      I don't know if you're familiar with it, but all I have to do is create a graph, and instead of putting a measure (say 'Number of visits) in either columns or rows, I add a calculated field that says something like this :

       

      IF [Paramètres].[Activity Measure] = 'nb_visits' THEN [nb_visits]

      ELSEIF [Paramètres].[Activity Measure] = 'nb_purchases' THEN [nb_purchases]

      END

       

      The [Paramètres].[Activity Measure] will be what the user chooses in the drop down menu above the graph.

       

      This works very well and I use it all the time. Although, there's one thing that sometimes prevents me from using it : i can only use one data format for the whole calculated field, even though sometimes the user will choose a ratio, a currently or an absolute value.

      Therefore, my ratio won't have '%' and my currencies will not have a '€' in the reports, among other issues.

       

      Would you know a trick to work around this presentation issue ?

       

      Thanks a lot,

       

      Hugo

        • 1. Re: Parameters and Metrics
          Joe Oppelt

          I had this exact problem.  Here is what I did.


          I have this calc:

           

                         [Current Values]

           

          // special note:  Certain [Metric] values are dollars.  Others are units.
          //  This calc turns the dollars into negative numbers.  We use a special custom formatting
          // on this calc to display negative numbers with a dollar sign instead of a negative. 
          // It's a hack, but it's pretty cute, eh?

          // the same technique is used for Target and Prior.

           

          if attr([Metric]) = "Donations" or attr([Metric]) = "Yield per Donor" then -1*SUM([Current Total]) else SUM([Current Total]) end

           

          So for my metrics that are dollars, I end up with a negative value.  For metrics that are units, I end up with the original value.  (In my report, all metrics are positive numbers, which is key for me.  More on this later.)

           

          Then in the Measures list I did right click on [Current Values], and chose "Default Properties" --> "Number Format".  Choose CUSTOM format, and enter this format string:

           

          #,###; "$ "#,###

           

          The semicolon (I highlighted it in red) tells Tableau to make a special format for negative numbers based on the format after the semicolon.  You'll probably want to do this for the % measure.


          The only drawback to this hack is that if you actually have a negative percent, this will blow up on you because your negative percent will be multiplied by -1 and turned into a positive.

           

          If that's the case, then consider creating a calc that outputs a character string, and it re-format your various measures according to the parameter chosen by the user.

          • 2. Re: Parameters and Metrics
            Hugo Portefaix

            Hi Joe, and thanks a lot for taking the time to help me !

             

            I've tried what you say, turned my ratio into into a negative value but the formula for the custom format doesn't seem to work. The format won't change, even though I can see on my chart the ratio data turned into negative values.

            My ratio are always positive so no problem around this.

             

            Would you have any idea what would be wrong ?

             

            Thanks,

             

            Hugo

            • 3. Re: Parameters and Metrics
              Joe Oppelt

              Upload a sample workbook.  At this point I would need to see something first-hand.

               

              Let me know what version of Tableau you are using so I can mess with it in the same version.