How to get 2 decimal places only

Gunjan
Gunjan Member

Hi,

I am using a beast mode calculation that results in a ratio for forward to manager : hire

CONCAT(Sum(CASE WHEN To Step = 'Forward to HM'THEN 1 ELSE 0 END)/(COUNT(DISTINCT Requisition Number)) , ':', Sum(CASE WHEN To Step = 'Hire'THEN 1 ELSE 0 END)/(COUNT(DISTINCT Requisition Number)) )

The result is this:

How do I get it to say 4.89 : 1.43 I've tried a bunch of things, but I can't get rid of the extra decimal numbers. Any help is much appreciated.

Thanks,

Gunjan

Best Answer

  • ColemenWilson
    Answer ✓

    Try:

    CONCAT(ROUND(Sum(CASE WHEN `To Step` = 'Forward to HM' THEN 1 ELSE 0 END)/(COUNT(DISTINCT `Requisition Number`)),2), ':', ROUND(Sum(CASE WHEN `To Step` = 'Hire' THEN 1 ELSE 0 END)/(COUNT(DISTINCT `Requisition Number`)),2))

    If I solved your problem, please select "yes" above

Answers

  • You can wrap each of the two parts of your concat with ROUND().

    CONCAT(ROUND(Sum(CASE WHEN To Step = 'Forward to HM'THEN 1 ELSE 0 END)/(COUNT(DISTINCT Requisition Number)),2) , ':', ROUND(Sum(CASE WHEN To Step = 'Hire'THEN 1 ELSE 0 END)/(COUNT(DISTINCT Requisition Number)),2) )

    If I solved your problem, please select "yes" above

  • Gunjan
    Gunjan Member

    Thanks @ColemenWilson there seems to be a syntax error. Not sure where.

  • ColemenWilson
    Answer ✓

    Try:

    CONCAT(ROUND(Sum(CASE WHEN `To Step` = 'Forward to HM' THEN 1 ELSE 0 END)/(COUNT(DISTINCT `Requisition Number`)),2), ':', ROUND(Sum(CASE WHEN `To Step` = 'Hire' THEN 1 ELSE 0 END)/(COUNT(DISTINCT `Requisition Number`)),2))

    If I solved your problem, please select "yes" above

  • Gunjan
    Gunjan Member

    OMG yes it worked Yay! Thank you so much @ColemenWilson