Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys.
I am getting through a very strange issue with Qlikview. Let's see:
I need to perform a calculation like this:
=If (myVariable = 1, num(sum(something), '#.##0,0'), num(sum(something_else), '#.##0,00'))
I've written that expression, and it's working fine.
Now, I need another expression to be calculated as a relative value (I've checked the Relative checkbox in the Expressions tab, and QV does its job fine). But as soon as I check the Relative checkbox and apply changes, the first expression becomes unformatted: instead of showing one or two decimals, it shows ten or twelve decimal positions.
I know I could uncheck the Relative checkbox and rewrite my expression as "Sum(whatever) / Sum(TOTAL whatever)", but is this normal? Is this any well-known incompatibility between Num function and Relative checkbox?
Our QV version is 10.0.9282.8 SR4, Windows 7.
Thank you all.
I have the same problem in QV version 11.20.13206.0 SR13 64-Bit Edition
I expect that is because the relative option internally wraps the expression with further calculations.The format statement embedded in the original expression is lost in that calculation, a bit like this:
Sum(Num(.....))
The format in the Num statement will be ignored, and the Sum() will use a generic numerical format because you have not specified a format for the Sum. Set the format for the relative expression in the Properties | Number tab.
I am working with multiple currencies so I can not use the standard formatting.
The column I am trying to format is not the one with the relative Checkbox checked.
To handle multiple currencies - any values are passed through vCurrency below to set number of decimal places and the prefix e.g $
SET vCurrency=if([CURRENCY DECIMALS]='0',money($1,[ISO PREFIX]&'#,##0'),money($1,[ISO PREFIX]&'#,##0.##'));
This works except for a table containing a column using vCurrency and another column with the relative checkbox checked.
This table is conditional upon a single currency being selected so different currencies are not added together.
If the relative checkbox is checked vCurrency appears to be ignored the value appears in standard format, if the relative checkbox is unchecked vCurrency takes effect and the currency prefix and correct number of decimals are displayed.
Like Ramon I have worked around the problem by manually calculating the relative value "Sum(whatever) / Sum(TOTAL whatever)"
Set the format for the dynamically formatted expression to Expression Default and st the format for the relative column to % using the Number format.
thanks Jonathan
it was already as you suggest
I have attached a simple example of the problem