topic Problem with Num and Relative expressions in QlikView

Problem with Num and Relative expressions

ramonarcusa36 — Wed, 12 Sep 2012 07:38:21 GMT

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.

Re: Problem with Num and Relative expressions

Tue, 21 Mar 2017 23:51:15 GMT

I have the same problem in QV version 11.20.13206.0 SR13 64-Bit Edition

Re: Problem with Num and Relative expressions

jonathandienst — Wed, 22 Mar 2017 05:32:40 GMT

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.

Re: Problem with Num and Relative expressions

Wed, 22 Mar 2017 21:53:28 GMT

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)"

Re: Problem with Num and Relative expressions

jonathandienst — Mon, 27 Mar 2017 04:45:52 GMT

Set the format for the dynamically formatted expression to Expression Default and st the format for the relative column to % using the Number format.

Re: Problem with Num and Relative expressions

Mon, 27 Mar 2017 22:40:04 GMT

thanks Jonathan

it was already as you suggest

Re: Problem with Num and Relative expressions

Tue, 28 Mar 2017 02:30:25 GMT

I have attached a simple example of the problem