Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
paul_ripley
Contributor III

How to show a number as a percentage

Hello

I have an expression (see below) that calculates a number.

=NUM(round(
(
If(floor(sum({$<period ={">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, pl = {F01}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1 = 0, '-',
floor(sum({$<period ={">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, pl = {F01}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1 )
-
If(sum({$<period = {">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, bud_account = {IS3}>} bud_amt ) = 0, '-',
sum({$<period = {">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, bud_account = {IS3}>} bud_amt) )
)
/

If(sum({$<period = {">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, bud_account = {IS3}>} bud_amt ) = 0, '-',
sum({$<period = {">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, bud_account = {IS3}>} bud_amt) )
*100
),'#,##0;(#,##0)')


For example, the above can give 14 or (14) but what I would like it to show is 14% or (14%)

When I change the last line to

),'#,##0%;(#,##0%)')

I get 1400% or (1400%)

Can ayone explain how I can get it to show 14% or (14%)

Many thanks

Paul

6 Replies
YoussefBelloum
Esteemed Contributor

Re: How to show a number as a percentage

Hi,

Divide the whole expression by 100

your_expression(...)/100

Re: How to show a number as a percentage

or in your case, do not multiply by 100

Capture.PNG

Re: How to show a number as a percentage

Percentage formatting assumes that your numbers are between 1.0 (100%) and 0.0 (0%). A velua of 14 then becomes... a lot.

This happens for both percentage format strings as well as for situations where you mark the "Show in Percentage (%)" checkbox on the Properties->Number tab.

As I mentioned in your other discussion, you might do away with the Num() call if you use the options on the Number tab instead of explicit formatting in your expressions. Makes expressions less complex.

paul_ripley
Contributor III

Re: How to show a number as a percentage

Yes unfortunately I have to present the numbers in N Printing so was hoping to be able use a formula, that's why I have been using NUM.

paul_ripley
Contributor III

Re: How to show a number as a percentage

Thanks for all your help.  I think the issue was a combination of the rounding and the *100.  When I remove the *100 the percentage of 14% becomes 0.14 and then the ROUND function rounded it down to zero

Re: How to show a number as a percentage

It will work with a formula, but you'll need to get your values to range from 0.0 to 1.0. That's why the other perfectly valid suggestions try to convince you to divide by 100 or omit a multiplication by 100.