Is there any way i can convert number to text format but still following the number format for comma

sarathchinta1

Unable to convert number to Text following comma format, Could anyone help me out of this problem?

 

Thanks in advance

 

 

 

 

rob_marshall

@sarathchinta1 

 

May I ask what exactly are you attempting to accomplish?  Why are you wanting to turn a number into a text?  I ask because that formula is nasty from a performance standpoint.

 

Rob

ChrisAHeathcote

@sarathchinta1 

Wozza, that is a crazy formula.

However, there are several hoops to jump through to return the correct text string.

I would echo @rob_marshall comment here and question why you need to do this. 

Here is my approach. 

I have split this out across multiple line items as there are several IF THEN ELSE statements that need to come together. The main issue I faced is ensuring that the reference used to pull in the thousands shifted depending on the length of the number. 

This example also only goes up to the millions.

 

 

 

 

 

 

 

 

sarathchinta1

For Requirement. But Problem has been solved. Thank you for your immediate response.

sarathchinta1

Thank you for immediate response. That would be great way to solve my problem.

MarkWarren

I would challenge that requirement!
You should try to avoid using text and converting numbers to text seems unnecessary.

Have a look at my recent article, Memory Usage From Text Concatenation  the same info applies here. Text manipulation creates a lot of memory overhead and uses (and throws away a lot of) memory in the calculation.

This can lead to problems at large scale model sizes.

DaneClemow

@MarkWarren & @rob_marshall , What do you think about this solution? It is certainly simpler in terms of formula writing, and curious your thoughts on memory usage..

Potential Solution: Create a list named 'Value to Text Conversions' and add the following items: Quadrillion, Trillion, Billion, Million, Thousands, Singles. Create a SYS module dimensioned by 'Value to Text Conversions' with a number Line Item named 'Denominator'. Populate the 'Denominator' line item with the values: 1000000000000000, 1000000000000, 1000000000, 1000000, 1000, 1. Also need a Line Item (Boolean) named "Display if Zero", and set only Singles = TRUE.

Then you need 3 line items to do the conversion: 1) 'Value for Text Conversions' (w/ 'Value to Text Conversions' dimension added), 2) 'Text Conversion - Pre-Agg', with same dimensions as #1, and then 3) 'Text Conversion - Post-Agg' which does not have 'Value to Text Conversion dimension included, just the original dimensions of the value you are converting. Then LI 1 formula should be: 'ROUND('Original Value to Convert' / 'SYS: Value to Text Conversions'.Denominator, 0, DOWN)'. LI 2 (text) should be 'IF 'SYS: Value to Text Conversions'.'Display if Zero?' OR 'Original Value to Convert' > 0 THEN RIGHT(TEXT('Original Value to Convert'), 3) ELSE BLANK'. And then finally LI 3 formula should be: TEXTLIST('Text Conversion - Pre Agg', ",", Value to Text Conversions, ALL), which produces the text formatted value, back in the original value dimensions.

Hear you guys on changing requirements, but sometimes when it comes to making something look good in a dashboard, using a pre-defined space, requires a little rule bending. But curious your guys thoughts on memory usage and how damaging this method would be.

Dane

MarkWarren

It's quite neat, but the TEXTLIST could be a performance issue at a large scale; but probably no likely to be worse than adding strings together with &.

I still don't see the need for it - use numbers and format for display…

DaneClemow

@MarkWarren the example of why I needed it: Needed values and % in two columns next to each other (the alternative approach of 2 different published grids side by side doesnt work because 1) looks bad and 2) takes up too much horizontal space and cant see everything you need to), and then rows were mix of values and text (so I couldnt put into LISS). For calcs, 100% agree, cant be converting to text- theres no valid purpose I can think of, but for displays- sometimes you have a space on a page that you cant alter and have things you have to fit there, that's why you could need it.

DaneClemow

Also should mention- current example above wont work for negative numbers.. Need to add a step (boolean) that tests if value is negative or positive, then do the above code on ABS(Value to Convert), and finally concatenate a "-" at beginning of text value if the boolean is true (it is negative).

DaneClemow

Update for everyone- the newest release in NUX where you can format line items or list items individually (card→format→format) has rendered this solution obsolete. Thank you @MarkWarren and Anaplan dev team for this truly design-changing feature!