New function to allow manipulation of numeric results as a string

A wish-list item related to numeric values (particularly as a result of Beast Mode calcs) for use in Summary Results.

 

I like to embed numeric data in a text, or compound message for my Summary.  For example, "25000 widgets were blah blah blah".  But i'd like it to reflect '25,000', not '25000' (not the thousands separator).  In order to accomplish this, would need a function that allows a numeric value to be converted to a text string where i can then use LEFT(), RIGHT(), etc., and then piece the components back together with a CONCAT().

**Say thank you by clicking the 'thumbs up'
**Be sure to select the answer that represents the best solution and mark as "Accept as Solution"
1
1 votes

· Last Updated

Comments

  • Thank you for this idea. I am assigning this to our product manager @ckwright for review.

  • @product_John @ckwright  I did figure out a workaround to this, had forgetten about this submitted idea.  I still think having a function that's specific to this action could be helpful, but as workaround you can combine the number with an empty-set using CONCAT(value, ''), which by default converts it to a string, and then use the SUBSTRING function to figure out where to break up the value.  It's a bit convoluted, probably more complicated than it needs to be, but it does work.  Here's the example code of how I did it... and I don't take credit for it.  Saw something similar done by another community member (wish I could recall who) and was able to adapt it.

     

    EX:

    CONCAT(
    CASE WHEN SUM(`value`) < 100000 THEN SUBSTRING(CONCAT(SUM(`value`),''),1,2) ELSE SUBSTRING(CONCAT(SUM(`value`),''),1,3) END,
    ',',
    CASE WHEN SUM(`value`) < 100000 THEN SUBSTRING(CONCAT(SUM(`value`),''),3,3) ELSE SUBSTRING(CONCAT(SUM(`value`),''),4,3) END,
    ' value As Of ',
    DATE_FORMAT(MAX(`End of Business Date`), '%b %d, %Y')
    )

    **Say thank you by clicking the 'thumbs up'
    **Be sure to select the answer that represents the best solution and mark as "Accept as Solution"
  • @ckatzman Glad to hear you were able to find a solution. We are looking into features for summary numbers, this is on the road map and will help solve this problem.

     

     

This discussion has been closed.