14 Replies Latest reply on Jul 17, 2018 2:09 PM by Nathan Schwinnen

# Number field as a dimension

I know this is probably answered on the forums somewhere, but I wasn't really sure how to search for it and get the results I'm looking for.

I have a calculated filed called "SCORE" that adds the value of five dimensions together.  "SCORE" can range from 1-5.  I want to calculate how many times each score occurred in comparison to the total, so that I have a table that looks like this:

Score     % of Total

1               20%

2               20%

3               20%

4               20%

5               20%

I'm having difficulty with this because Tableau wants to sum the numbers together, not list them out separately.  I have tried a couple of calculated fields to turn "SCORE" into a string:

STR([SCORE])

and

IF [SCORE] = 1 THEN "*"

ELSE IF [SCORE] = 2 THEN "**"

Either way, Tableau still wants to aggregate these calculated fields.

Any help would be appreciated.

• ###### 1. Re: Number field as a dimension

I Think you can Create a Lod for this. If you post some sample and let's know what is desired, we may help.

Thanks

Deepak

• ###### 2. Re: Number field as a dimension

Deepak - I have attached an Excel file that I hope will be sufficient.

In a nutshell, I have specific events that are supposed to happen during a transaction.  I have created calculated fields so that if an event happened, the result will be 1 and if 0 if it did not.  I am adding up each of these events to get a score for each transaction.  So, you can score from 0-5.

Now, what I want to know is how many transactions scored 0, how many scored 1, etc.

In the Excel file attached, there is some data and then to the right there is a chart.

 SCORE Number of Records % of Total 1 3 8.8% 2 14 41.2% 3 7 20.6% 4 9 26.5% 5 1 2.9%

This chart is all I'm looking for, but as I stated previously, Tableau does not want to list out numbers like that on a chart.  If I add "Score" to the shelf, Tableau is going to aggregate these values.

This is a simple version of what I am doing.  I am actually connecting directly to a database with thousands of records.

• ###### 3. Re: Number field as a dimension

Nathan -- Are you saying you have the % of Total already calculated in a calc somewhere?

Put that on the data shelf and change it from CONTINUOUS to DISCRETE.  That will change the pill from green to blue.  Drag that blue pill onto the columns shelf.

• ###### 4. Re: Number field as a dimension

If I missed the boat with that reply, hack up a sample workbook using an extract of a couple dozen rows from your data.  (Shove that subset into excel and anonymize proprietary data if necessary.)

• ###### 5. Re: Number field as a dimension

I do not have % of total anywhere.  I can calculate the % of total easily enough.  This issue in not related to calculating totals or LOD calculations.  The issue is just that "SCORE" is a measure and I need it to be a dimension.  I have had other fields that are numbers that I have been able to convert to dimensions, but this one I can't, possibly because it is a calculated field.

I can try to upload a better version of what I am actually working on.

• ###### 6. Re: Number field as a dimension

Oh.  I get it.

Yes.  Once you have aggregates of any sort on a calc, it can only be a measure.

• ###### 7. Re: Number field as a dimension

Not sure these attachments will even help.  In my real workbook there are many more calculations, but obviously when I download the data to Excel, these are no longer calculations.  As you can see from the workbook, however, when score is added to the shelf, Tableau sees this as a number and aggregates it.  I want them to be listed out as I displayed above.

• ###### 8. Re: Number field as a dimension

Well, that's a bummer.  I'll have to try to find a different way to do this.

• ###### 9. Re: Number field as a dimension

But if you make the calc DISCRETE, you can still make it behave like a dimension (for most things.  You won't be able to run table calcs along that "dimension", for example, or LODs.)

What dimensional behavior are you trying to get out of it?

• ###### 10. Re: Number field as a dimension

Just trying to make a simple chart like the one I pasted above.  SCORE is a measure but I want it to be a dimension so I can calculate the number of times each customer (in my case it is a carrier) has achieved each score.

• ###### 11. Re: Number field as a dimension

See attached.  I created "Bins" for Score.

(Turns out I didn't need to make my own copy.  I just wanted to make sure I was controlling everything.)

• ###### 12. Re: Number field as a dimension

What are the requirements for a field to be able to use it to create bins? I cannot create bins with the field I am using in my original workbook.  the only two options under create are calculated field and parameter.

• ###### 13. Re: Number field as a dimension

Might not be able to set bins for aggregates.  I'm almost certain you can't do it with table calcs.

Can you hack up an additional calc like the one you are trying to use, and add it to your example workbook?  I'll see what I can do to get you what you need.

• ###### 14. Re: Number field as a dimension

Thanks for the help, Joe.  I will try to come up with something tomorrow.