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!

kjacokes

Generally agree with Dane here, I've encountered several compelling reasons to format numbers as text to help customers get the most value out of the platform, without having to sacrifice performance, workspace, or maintainability.

The new formatting enhancements in the UX layer create some valuable and necessary tools, though I still find value in having a number-to-text formatting layer in place to help users with narrative reporting, statutory reporting, variance reporting and explanations.

Here's one such example, where we have slides where we need to weave together text and numbers.

I routinely do the same thing for variance reporting to aggregate commentary (e.g., for the top 10 absolute $-based variances of vendor spend, write a sentence like In reporting period [Quarter #] spend with Vendor [XYZ] was over/(under) by [budget vs. actuals variance $ amount]. [$] of this variance was attributable to FX fluctuations, [$] of this variance was attributable to timing differences, [$] of this variance was attributable to over-spend due to [commentary from budget owner].

When creating this sort of user experience where users need the ability to define a large number of KPIs, define their formats, and then plug those formatted figures into narrative commentary, the user experience has to be super straightforward for us to get good adoption and to delight end users, build enthusiasm, and create net promoters of a solution like Anaplan.

So I end up doing something like this:

I'm sure I'll get a little bit of hate for the string manipulation formulas here. ;) But we can mitigate the risk of performance issues by breaking the daisy-chain and having this logic be in a separate model that is lean and mean, such that we're not doing these calculations on numbers that plug into a ton of upstream line items. At the end of the day, if enough customers tell me they need this, I'm

Using this approach you can have a fully dynamic dashboard-driven user experience that enables a business user to create KPIs, define how they're calculated, define how they're formatted, and layer in text in a structured manner that enables narrative and statutory reporting without being forced to leave the page.

NOTE: The above is not offered as the best approach or even a recommended approach. There are many customers who are better off leveraging the D365 or GSuite Add-ins because they need that flexibility. Fluence might be better for others who have many users collaborating on a deliverable and are leveraging data not available within Anaplan. The above approach seeks to help senior stakeholders, who have a lean team, codify their narrative and statutory reporting processes, automating as much if it as possible.

Oh and here's the blueprint that brings it together. There's surely a way to simplify and streamline this module, but the below is what I'd consider a v1.0.