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.
Prueba_.qvw 159.2 K
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....
" 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.
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.
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 :-)
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.
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%.
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.