Number and text in summary value

AttuAk

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?

NewsomSolutions

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

NewsomSolutions

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

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.