Number and text in summary value

AttuAk
AttuAk Member
edited March 2023 in Scheduled Reports

I need to show a value along with text in the summary number.  I know this can be done by concatnating but for some reason I can't get the value to show as a summary number.  This is what I need:

Show a count of distinct records (which is 10,232) and then add some text.  The issue is when I concatnate the count(distinct records) and add the text, I loose the comma.  So, I'd like to take the first two digits here (10) concatnate a ',' and then add the last three digits (232) and then add my text.  Is this possible to do?  Any suggestions?

Best Answer

  • NewsomSolutions
    NewsomSolutions Domo Employee
    Answer ✓

    This may get tricky if you just need to have the thousand seperator in your text.  But since the 10232 is a string (post concat) you could maybe do something like

    concat(

    (case when length(10232field) > 3 then

    substring(reverse(10232field),1,3),',',substring(reverse(10232),4,3)

    else 10232field

    end),

    , 'blah blah blah'

    )

     

    I haven't tested this, but the reverse should allow you to insert the comma after the 3 digits...the case is there in case your count is below 1k.  If you a count over a million.you may need to add some logic for that too.

     

    Late at night so be cautious of my syntax or logic.  ?

     

    Matt

Answers

  • NewsomSolutions
    NewsomSolutions Domo Employee
    Answer ✓

    This may get tricky if you just need to have the thousand seperator in your text.  But since the 10232 is a string (post concat) you could maybe do something like

    concat(

    (case when length(10232field) > 3 then

    substring(reverse(10232field),1,3),',',substring(reverse(10232),4,3)

    else 10232field

    end),

    , 'blah blah blah'

    )

     

    I haven't tested this, but the reverse should allow you to insert the comma after the 3 digits...the case is there in case your count is below 1k.  If you a count over a million.you may need to add some logic for that too.

     

    Late at night so be cautious of my syntax or logic.  ?

     

    Matt

  • AttuAk
    AttuAk Member

    This worked out great.  Thank you for the suggestions here.  I did change it a bit, did not use the 'reverse' but got it to work based on your input.  This is what worked:

     

    CONCAT(SUBSTRING(CONCAT(`10232field`,' '),1,2),',',SUBSTRING(CONCAT(`10232field`,' '),3,3))

     

    Since my number is not really changing I was able to avoid the CASE WHEN logic to where the number grew, otherwise I would just have to add that.

This discussion has been closed.

Categories