0

Truncating decimal numbers in dimensions

I have a custom model that has currency exchange rates; the rates are decimal numbers with 4 places, in the sql query I have them truncated to only show 4 places

In some of my discoveries, I need to show the rate in rows rather than in measures so I have it set both as a dimension and as a measure. When added to a discovery, the dimension in rows is showing with trailing zeroes as in the attached picture; in the measures it shows correctly as I have formatted it that way in the model columns

Is there a way to format the dimension so it shows the same as in the sql query? 

5 replies

null
    • VP Product Management
    • Ian_Macdonald
    • 3 yrs ago
    • Reported - view

    Hi Sophie,

    In your data flow  in the model, add a calculated field into the table that contains the Rate field, Format it using the Format() function. This will format the decimal to the required decimal places and convert it to a string. Use the string version as a Dimension and the numeric version as the Measure:

    Hope that helps.

    Ian

      • Lead Consultant Advanced Analytics
      • Michael_Daun
      • 5 days ago
      • Reported - view

       - thank you for this. As I had a similar requirement some days ago I like to ask two questions: 

      1. Is it possible to format the decimal to the required decimal places and leave it as a NUMBER, i.e. not convert it to a STRING? If this is possible, numerical dimensions could be displayed right-aligned.
      2. How to achieve the desired number format when using a Direct Model? I fumbled around in the "Columns" section of the model definition, i.e. I tried to format an additional Custom Column for this dimension. But this led to frightening formulate expressions, as there are no formatting functions, but only string functions. 

      Sounds to me a like a sensible Feature Request: Formatting for dimensions - similar to measures - in the model definition. Or, if not possible, FORMAT functions for Custom Columns. 

      Regards, 
      Michael

      • Customer Solutions Architect
      • Moshe_Yossef
      • 3 days ago
      • Reported - view

       

      First of all notice this is an old post - today you can also use custom columns to achieve this - that would work in direct query as well (albeit - I would recommend adding the column in the data source if possible).

      I noticed that in DQ models, if the data type is Numeric(something,2) Pyramid will only show 2 decimal digits.

      If you're using IMDB, you can add a calculated column that takes the numeric column and set it to decimal (38,2) - however the 2 digits can't be changed.

      Unfortunately there isn't a way to change the number of decimals in Pyramid at the moment - could be a nice idea,

       

      Do notice that if the change is just formatting, you may end up with 20 different members named "9.01" because it will represent multiple values with higher accuracy.

      • Lead Consultant Advanced Analytics
      • Michael_Daun
      • 3 days ago
      • Reported - view

      , THANKS for bringing this to my attention - especially the age of this post! I dunno how it came into my field of view...

      Anyway - with the Custom Column I could use ToNumber (which works) but I have no functions to format, unless I try to do it via string functions. Correct? This would be the product idea, you mentioned?

      • Customer Solutions Architect
      • Moshe_Yossef
      • 3 days ago
      • Reported - view

       Yes, you will need to convert it to the string.
      The idea can be adding scale to ToNumber() or formatting number columns in the model (you can already format date fields, by the way).

Login to reply

Content aside

  • Status Answered
  • 3 days agoLast active
  • 5Replies
  • 70Views
  • 7 Following