Getting a percentage in a Text Object

[#1]

Hi all,

So I have a text object and an expression that I would like made into a percentage. My expression works perfectly but I cannot seem to format the value 0,202172078143 that I get into a percentage.

My expression is:

SUM({<BLART={'K1','K2','K3','K4','KR'}, [BA Name]={'Jackets'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM({<BLART={'KA'}, [BA Name]={'Jackets'}>}#ActualNetSpend_NOK)) / (SUM(TOTAL{<BLART={'K1','K2','K3','K4','KR'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM(TOTAL{<BLART={'KA'}>}#ActualNetSpend_NOK))

I tried adding the following to the end (and trying several options with where to place parenthesis but only get an error message. I am sure that there is only a small syntactical error:

..........ART={'KA'}>}#ActualNetSpend_NOK)), '#,##0.0%')

[Anonymous]

used this in textobject and it Shows 12,45%

=num('12,45','#.#0%')

[maxgro]

add the bold

num(

SUM({<BLART={'K1','K2','K3','K4','KR'}, [BA Name]={'Jackets'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM({<BLART={'KA'}, [BA Name]={'Jackets'}>}#ActualNetSpend_NOK)) / (SUM(TOTAL{<BLART={'K1','K2','K3','K4','KR'}, BUZEI={'001'}>}#ActualNetSpend_NOK) +SUM(TOTAL{<BLART={'KA'}>}#ActualNetSpend_NOK))

, '#,##0.00%'

)

you can copy the format from number tab

[#4]

Hi Massimo, I cannot format the number as this is a Text Box. The formatting has to be done in the expresssion I think.

[sunny_talwar]

Tried this:

Num(SUM({<BLART={'K1','K2','K3','K4','KR'}, [BA Name]={'Jackets'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM({<BLART={'KA'}, [BA Name]={'Jackets'}>}#ActualNetSpend_NOK)) / (SUM(TOTAL{<BLART={'K1','K2','K3','K4','KR'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM(TOTAL{<BLART={'KA'}>}#ActualNetSpend_NOK)), '#,##0.0%')

[avinashelite]

use NUm() function around your expression like this :

NUM(your_expression, ########,###,###%')

[#7]

When I do the Num expressions suggested above I keep getting an error saying "Garbage after expression: ","

=NUM(SUM({<BLART={'K1','K2','K3','K4','KR'}, [BA Name]={'Jackets'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM({<BLART={'KA'}, [BA Name]={'Jackets'}>}#ActualNetSpend_NOK)) / (SUM(TOTAL{<BLART={'K1','K2','K3','K4','KR'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM(TOTAL{<BLART={'KA'}>}#ActualNetSpend_NOK)),'#,##0.0%')

[#8]

Another way I could do it would be to just show the closest integer. So instead of 0,2021727 I multiply by 100 and just show 20. I can then add & '%' at the end to show 20%. How do I script to get it to be an integer?

[sunny_talwar]

‌=NUM((SUM({<BLART={'K1','K2','K3','K4','KR'}, [BA Name]={'Jackets'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM({<BLART={'KA'}, [BA Name]={'Jackets'}>}#ActualNetSpend_NOK)) / (SUM(TOTAL{<BLART={'K1','K2','K3','K4','KR'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM(TOTAL{<BLART={'KA'}>}#ActualNetSpend_NOK)),'#,##0.0%')

[Colin-Albert · Accepted solution]

Check the pairs of opening and closing brackets are correct.

You should have something like

     num (  (expr1 + expr2) / (expr3 + expr4) , format_expr)

I think you need to change you expression to

=NUM( (SUM({<BLART={'K1','K2','K3','K4','KR'}, [BA Name]={'Jackets'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM({<BLART={'KA'}, [BA Name]={'Jackets'}>}#ActualNetSpend_NOK)) / (SUM(TOTAL{<BLART={'K1','K2','K3','K4','KR'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM(TOTAL{<BLART={'KA'}>}#ActualNetSpend_NOK)),'#,##0.0%')