3 Replies Latest reply on Jun 1, 2016 3:33 PM by Joe Oppelt

    How to handle multiple number formats in one field?

    Sarah McGraw

      Hi everyone, this is my first post, so please bear with me....

       

      I have a need to create a simple data table to accompany visualizations. I'm having multiple problems, but the first one is this: I have multiple fields with two different number formats. The data contains records with names, denominators, numerators (either # or $, depending on the record), targets (either % or %), and actuals (% or $)  This is the way the data comes in, so it's not too practical to reformat the source data.

       

      In the past, I've made simple tables in excel organized thusly:

       

      --------------numerator------denominator--------actual-------target--------achievement

      name1---------###--------------####-----------------%------------%-----------------%

      name2---------$$$--------------####-----------------$$-----------$$-----------------%

      name3---------###--------------####-----------------%------------%-----------------%

       

      as well as charts with more than one y axis, so the money gets plotted against the left, regular axis, and the percents get plotted against the right one. (And, yes, I also haven't found a way to get both types of data into the same Tableau graph, either, since the %s are all less than 1 and the $$ is all in the 5 digits or greater.)

       

      I've found this workaround on line: http://vizwiz.blogspot.com/2015/01/formattinghack.html, which DOES work for formatting the table, but obviously doesn't help with the chart at all.

       

      What is your recommendation for dealing with data set up like this? Is it better to find a way to split the data into two fields to begin with (and then combine it in tableau so it fits in the chart?). Are there workarounds I should know for dealing with the data as it is? What would you do?

        • 1. Re: How to handle multiple number formats in one field?
          Joe Oppelt

          You say it's not too practical to reformat, but that might be your only option.

           

          I'd like to see a working example of this so that I can mess with it with you and see if we can hack something up.

           

          Create an excel file of bogus data that demonstrates what you are facing.  Probably only need 4 or 5 rows in it, really.  Then hack up a simple sheet based on that, and we can work together from there.  Let me know what version of Tableau you are doing this on so that I can work on the same version.

           

          One question I would want to know:  How would you determine that the [Target] for "Name1" should be a percent, and the [Target] for "Name2" should be dollars?  (And the same for all the other measures.)

          • 2. Re: How to handle multiple number formats in one field?
            Sarah McGraw

            screenshot.PNG

            Hi Joe, and thanks for your help. Here's a screenshot of some fake unformatted data (didn't see a spot to upload a file--did I miss it?)

             

            In general, I have a number of programs (AAA, BBB, and CCC), each of which needs to be evaluated across a number of separate measures (Measure1, Measure2, and Measure3). As you can see, Measure1 is a financial measure and the others are not.

             

            For each measure, I need to take a numerator (either a number of units or a dollar amount, depending on the measure) and a denominator (always a number of units). Then, the resulting quotient ('actual') is either the profit-per (a $$ field) or percentage 'success (a percent).

             

            That then needs to be compared to a target, also either a percentage or currency.

             

            The actual and target are then compared to produce a % achievement, which is of course always a percent.

             

            Ideally I can wrangle this into a dynamic table (using location and FYQtr as filters) which can allow a user to combine multiple locations and/or quarters or single them out as he/she sees fit. In reality I have a lot more locations and quarters to include, as well as additional programs and measures. However, I think this screenshot shows the data format issue pretty well.

            • 3. Re: How to handle multiple number formats in one field?
              Joe Oppelt

              When you are writing your reply, look on the upper right corner of the edit box.  "Advanced editor and add an attachment".  Click that.


              When you are in THAT editor, in the lower right corner is "Attach".  click that.  That's how you attach an actual file.

               

              So create an excel file of bogus data.  Screen shot doesn't help me.  About all I can do with that is manually type it all into excel and try to recreate what you are doing...  Create a workbook based on that excel file.  Make it look somewhat like your actual sheet.  THAT's what I need to have.

               

              I want an actual working packaged workbook I can use.  Get me as close to where you want to go as you can, and then I'll be able to take it up from there.  Remember, I have my own job to do.  I can't spend a lot of time trying to recreate what I am guessing you are doing.  Get me there, and I can start doing something with that.