topic Re: Getting a percentage in a Text Object in QlikView

Getting a percentage in a Text Object

Mon, 20 Jul 2015 11:41:11 GMT

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%')

Re: Getting a percentage in a Text Object

Anonymous — Mon, 20 Jul 2015 11:44:50 GMT

used this in textobject and it Shows 12,45%

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

Re: Getting a percentage in a Text Object

maxgro — Mon, 20 Jul 2015 11:45:36 GMT

add the bold

num(

, '#,##0.00%'

)

you can copy the format from number tab

Re: Getting a percentage in a Text Object

Mon, 20 Jul 2015 12:18:43 GMT

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

Re: Getting a percentage in a Text Object

sunny_talwar — Mon, 20 Jul 2015 12:20:24 GMT

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%')

Re: Getting a percentage in a Text Object

avinashelite — Mon, 20 Jul 2015 12:22:08 GMT

use NUm() function around your expression like this :

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

Re: Getting a percentage in a Text Object

Mon, 20 Jul 2015 12:31:14 GMT

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%')

Re: Getting a percentage in a Text Object

Mon, 20 Jul 2015 12:33:11 GMT

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?

Re: Getting a percentage in a Text Object

sunny_talwar — Mon, 20 Jul 2015 12:35:19 GMT

‌=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%')

Re: Getting a percentage in a Text Object

Colin-Albert — Mon, 20 Jul 2015 12:35:41 GMT

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%')

Re: Getting a percentage in a Text Object

Mon, 20 Jul 2015 12:37:49 GMT

That's perfect Colin. The extra parenthesis at the beginning did it! 🙂