7 Replies Latest reply on Jun 2, 2016 7:52 AM by Gabriel Almeida

    Is there a way to have a Custom Conditional Number Format for KPIs?

    Jose Pedro Toma

      Is it possible to place conditions on the  "Custom Number Format"?

       

      The use case is a dataset that has a monthly KPI values. The KPI name is in one column and the Value in another. For Example:

       

      KPIValueFormat Type
      Sales100#
      Instock %85%#%
      Process Improvement GradeGoodText

       

      So what I would like is to be able to use just 1 field and have it format conditionally based on a "Format Type". This would allow me to use one same worksheet for Multiple KPIs. (e.g. pivoting ths source data is not desirable)

       

      Any ideas on how to achieve this?

       

      Seabstian

        • 1. Re: Is there a way to have a Custom Conditional Number Format for KPIs?
          Mark Holtz

          Currently up through Tableau 7.0, conditional formatting is not available. I think it was on the wish list for future releases, but who knows when that will be. For now, if you have to "fake it."

           

          Conditionally formatting numbers and %'s in a single field is possible using calculated fields that will turn all values into strings. (probably not ideal) You can incorporate $ numbers and non-$ numbers here as well. Another drawback here is that these values would no longer be able to be used in measure calculations. You'd have to un-convert them, which would probably be very tedious...

           

          If you are going to have many KPIs with each format type, you could also consider creating a different column for #, % and Text values depending on what is appropriate for the given KPI... something like this:

           

          KPI# value% valuetext value
          Sales100nullnull
          Instock %null85%null
          Process Improvement GradenullnullGood
          1 of 1 people found this helpful
          • 2. Re: Is there a way to have a Custom Conditional Number Format for KPIs?
            Jose Pedro Toma

            I guess that would work of for numbers on a table. The problem would be with using them in graphs. For example, I have a Drop-Down List where I choose 1 from a list of 50 KPIs.

             

            When I choose the KPI, I have a monthly line chart below that shows the KPI evolution. Very simple, but I guess the axis formatting will be broken.

             

            The alternative is making 2-3 types of graphs, one for each type of KPI number format. But then, how can I use the same combo-box to show all KPI's but use a particular view for each?

            • 3. Re: Is there a way to have a Custom Conditional Number Format for KPIs?
              Jose Pedro Toma

              Also, how do you suggest converting the data to string?

               

              For example, I have in one same "Value" field both Sales=123,5993 USD and %Margin = 0.213432.

              When I make a calculated field all I have to convert the value to a string is the STR() function, but this doesn't manage number formatting... I guess I could try with round() and build my own formats...

               

              Any ideas?

              • 4. Re: Is there a way to have a Custom Conditional Number Format for KPIs?
                Mark Holtz

                For the Sales and %Margin, I was thinking of something like:

                IF [Value] <= 1 AND [Value] >= 0 // Implies a percent
                // you could also just hard-code the names of each metric that should be formatted as a %
                THEN LEFT(STR([Value]*100),2)+'.'+MID(STR([Value]*100),2,2)+'%'
                ELSEIF LEN(STR([Value])) <= 3 THEN STR([Value]) // up to hundreds place
                ELSEIF LEN(STR([Value])) <= 6 AND LEN(STR([Value])) > 3 THEN LEFT(STR([Value]),LEN([Value])-3)+','+RIGHT(STR([Value]),3) // thousands to hundred thousands place

                END

                1 of 1 people found this helpful
                • 5. Re: Is there a way to have a Custom Conditional Number Format for KPIs?
                  Jose Pedro Toma

                  Thanks,

                   

                  I tested this and it is closer to what I'm looking for. I've taken the "Value" and created a "Value Label". I've attached a packaged workbook with this.

                   

                  A couple of problems that remain:

                  1. formatting still needs details (large numbers, %>100 and <10, etc)
                  2. you lose aggregation abilities on the field once it becomes a string dimension (label)
                  3. when graphing, the axis only handle the default value aggregation. Value Label on the text mark helps, but the axis do look wierd. I guess this would requiere a conditional axis format.

                   

                  more ideas/suggestions are welcome!

                  • 6. Re: Is there a way to have a Custom Conditional Number Format for KPIs?
                    Mark Holtz

                    Another thought I had was that it might be useful to have a [FormatType] field to designate whether the measure should be a percent, $-figure, text field, or integer field (formatted with comma thousands separator).

                     

                    Then, for the if statement instead of [Value] <= 1 AND [Value] >= 0 or anything else, you could know for certain:

                    IF [FormatType] = 'Percent' THEN STR([Value*100])+'%'
                    or something like that.

                     

                    That way, for aggregation, you can "reverse" the string conversion if you so desire.
                    I still think this is a lot messier than having separate measure columns for like types of data types. Have a % column for % KPIs, a $ column for $ KPIs, a # column for non-$ KPIs, and a text column if needed. Each metric would be null/blank in all but one of the fields.  That allows for aggregation more easily.

                     

                    Regarding using them in a graph, trying to graph $ and % at the same time probably wouldn't work very well anyways.  So, perhaps you could utilize a parameter to switch out the data.

                     

                    Something like 'Select Measure' which would hold string "names" of each measure the user could select to switch onto the graph (one at a time). Then create a calculated field to "toggle" the measure:
                    IF [Select Measure] = 'Total Sales' THEN [Total Sales Metric]
                    ELSEIF [Select Measure] = 'Profit Margin (pct)' THEN [Profit Margin]
                    END
                    You could then put this calculated "dynamic" measure in the view or graph...

                    1 of 1 people found this helpful
                    • 7. Re: Is there a way to have a Custom Conditional Number Format for KPIs?
                      Gabriel Almeida

                      Does any news about this thread?

                       

                      I have the same problem.