topic Displaying negetive percentage values as (##%) in QlikView

Displaying negetive percentage values as (##%)

vlad_komarov — Tue, 08 Jul 2014 19:06:13 GMT

Hello all,

I need to display negative percentage values in the chart in the (##%) format (as (5%), (10%) instead of -5%, -10%).

Here is the default QV formatting:

using the '#,##0%' format string

I thought displaying negative percentage values in parenthesis should be a trivial task and I've updated the chart with the following format string '#,##0%;(#,##0%)'.

As a result values are displayed like:

I am not 100% sure how QV is using this format string, but it looks like it doubles "Show in percent %" option and divides values by 100 one more time (for negative values only).

I was hoping somebody have more elegant solution instead of using (Var *100) option for negative values in my Expression....

The closest one I've found is to use the '#,##0%;(#,##0)%' format string (% sign outside the parenthesis).

This display format is a bit un-orthodox, but at least the values are correct.

Any other solutions/suggestions? I would appreciate a feedback.

Best regards,

Vladimir

Re: Displaying negetive percentage values as (##%)

Tue, 08 Jul 2014 22:38:42 GMT

Hi Vladimir

The following qvw, contains 4 tables:

1 -. Chart with percentage number format (simple).

2 - Table with the format #, # # 0.00%.; (#, # # 0.00)%.

3 - Table in the format expression.

4 - Table with the same number format for Actual and Budget

waiting you need this information.

Re: Displaying negetive percentage values as (##%)

vlad_komarov — Tue, 08 Jul 2014 23:03:32 GMT

Daniel,

Thank you for suggestions.

But I really would like to avoid using IFs in Expressions....

so using num (( ( ([Actual] / [Budget]) -1)*100, '#,##0%; (#,##0%)' ) with some values conditions does not look like a elegant solution.

My main concern is why QV interprets the format string this way.

Seems like a bug to me....

Re: Displaying negetive percentage values as (##%)

rwunderlich — Tue, 08 Jul 2014 23:18:00 GMT

Hi Vladimir,

" am not 100% sure how QV is using this format string, but it looks like it doubles "Show in percent %" option and divides values by 100 one more time (for negative values only)."

I interpret the behavior differently. To me it looks like % within the () has no effect. That is, the * 100 is not occurring, It's the raw value that is being displayed and the % is being inserted as a literal character only.

So it looks like you can't include the % within the (). But you can put it outside or use "-nn%".

As to whether it's a bug, there is little documentation on the formatting rules but it's worth running by support.

-Rob

Re: Displaying negetive percentage values as (##%)

Clever_Anjos — Wed, 09 Jul 2014 00:29:38 GMT

Use this formatting expression: #.##0,00%;(#.##0,00)%

Re: Displaying negetive percentage values as (##%)

Clever_Anjos — Wed, 09 Jul 2014 01:06:06 GMT

Sorry, Daniel Degollado presented same solution

Re: Displaying negetive percentage values as (##%)

vlad_komarov — Wed, 09 Jul 2014 03:16:50 GMT

Rob,

Thanks for reply. You probably right about the the reason QV handles this formatting string this way.

It would be actually nice if QV would allow variables or composite strings to be used in the Number formatting section.

In this case the issue might be resolved with some code like '(#,##0' & chr(37) & ')'....

Using % sign outside the parenthesis creates a non-standard way to display a negative numbers, so I rather use a default format [-9%] instead.

Re: Displaying negetive percentage values as (##%)

vlad_komarov — Wed, 09 Jul 2014 03:19:05 GMT

Clever,

Yes, I know about the (9)% option, but I do not like it. It confuses people, IMHO.

I prefer to use the default format [-9%] instead.

Re: Displaying negetive percentage values as (##%)

rwunderlich — Wed, 09 Jul 2014 18:46:36 GMT

"It would be actually nice if QV would allow variables or composite strings to be used in the Number formatting section."

Yes! I've long wanted to be able to use expressions in format property.

-Rob

Re: Displaying negetive percentage values as (##%)

vlad_komarov — Wed, 09 Jul 2014 19:02:43 GMT

Rob,

May be some day it will be implemented.... I've got e-mail just couple days ago that the functionality I've asked about last year was implemented in the latest SR release (GetSelectedCount and Alternative State).

So, I do believe in miracles 🙂

Re: Displaying negetive percentage values as (##%)

d_pranskus — Sat, 07 Feb 2015 12:43:23 GMT

Actually I came across this discussion. What I found that the simplest solution is to multiply the value in the expression and use the following text in the format: #,##0.0% ;(#,##0.0%) (Notice the space before the semicolon).

Qlikview treats the format as a percentage if it is the last character in the format.

For example

NUM((SUM(profit)/SUM(amount) -1) * 100, '#,##0.0% ;(#,##0.0%)')

The only problem I see here if this exported into Excel, it appears without percent sign or if you use the format as a setting, it shows like 25,00%.

Cheers

Darius

Re: Displaying negetive percentage values as (##%)

vlad_komarov — Wed, 11 Feb 2015 21:39:20 GMT

Darius,

Thank you for reply. This solution will not work well in my case (I am using variables as expressions, which actually integrated into other charts, so multiplying all values by 100 creates few problems). And the full export functionality (with proper formatting) is critical for me as well.

Thanks for suggestion.

Regards,

Vladimir

Re: Displaying negetive percentage values as (##%)

inoruoya — Tue, 07 Jun 2016 18:45:55 GMT

Thanks for your idea @Darius Pranskus