topic Re: Conditional Number Formatting with Custom Parameter Variable in QlikView

Conditional Number Formatting with Custom Parameter Variable

Wed, 14 May 2014 16:44:51 GMT

Hello,

I am having trouble with conditional number formatting for parameter variables. I have attempted solutions from the following discussion threads without success:

Formatting expression for both % and Numbers

Conditional Number Formatting

To simplify - let's say I have two variables, 'PCT_VAR' and 'INT_VAR'. PCT_VAR should be represented as a percentage and INT_VAR should be represented as an integer. I have created a variable, 'vMETRIC', to pass as a parameter.

My Number Format Settings are set to 'Expression Default'. My expression looks like this:

IF(vMETRIC = 'PCT_VAR', NUM(SUM($(vMETRIC)), '#,###.00%'), NUM(SUM($(vMETRIC)), '#,###'))

I have also tried being more explicit with each variable:

IF(vMETRIC= 'PCT_VAR', NUM(SUM($(vMETRIC)), '#,###.00%'),

IF(vMETRIC= 'INT_VAR', NUM(SUM($(vMETRIC)), '#,###')

)

These fail to format PCT_VAR as a percentage (it shows as a decimal). I would appreciate any help figuring this out!

Note: I cannot attach the workbook, but will create a 'dummy' workbook with the above example if my explanation was not clear enough.

Thanks for the help!

Re: Conditional Number Formatting with Custom Parameter Variable

clisboa_noesis — Thu, 15 May 2014 09:29:47 GMT

Hi,

Do you have the Number Format Settings in the Number tab set do Expression Default?

Best Regards,

Carlos

Re: Conditional Number Formatting with Custom Parameter Variable

crusader_ — Thu, 15 May 2014 09:44:01 GMT

Hi,

Check this one.

test:

LOAD * INLINE [

PID, Person, Amount, Metric

1, Mary , 123.23, 'PCT_VAR'

1, Collin, 44.33, 'PCT_VAR'

1, Camaro, 443.2, 'INT_VAR'

2, Mary, 99.2, 'INT_VAR'

2, Camaro, 88.99, 'PCT_VAR'

];

And Chart Expression:

if(Metric='PCT_VAR', num(sum(Amount),'####.00%'),num(sum(Amount),'#,###'))

HTH

Andrei

Re: Re: Conditional Number Formatting with Custom Parameter Variable

Thu, 15 May 2014 14:01:47 GMT

Hi Andrei, thanks for the response. Unfortunately, my data is quite large and has variables transposed across as columns. Something like this:

PID	Person	INT_VAR	PCT_VAR
1	Mary	157123	.2342
1	Collin	415612	.3445
1	Camaro	233345	.6754
2	Mary	16664	.2312
2	Camaro	1234566	.3456

I create a variable vMETRIC to pass each data column as a parameter into the chart object. This allows the user to switch between INT_VAR and PCT_VAR at will. This data transition works perfectly, but the number formatting does not work in tandem. Any ideas on how this can be accomplished with the above data structure?

Thanks!

Jason

Re: Re: Conditional Number Formatting with Custom Parameter Variable

Thu, 15 May 2014 14:02:29 GMT

Hi Carlos - yes Expression Default

Re: Re: Conditional Number Formatting with Custom Parameter Variable

clisboa_noesis — Thu, 15 May 2014 14:56:45 GMT

If your PCT_VAR already has the % value you can't use sum(PCT_VAR) in your expression.

That way you're making a sum of % which is probably not what you want.

Regards,

Carlos

Re: Re: Conditional Number Formatting with Custom Parameter Variable

Thu, 15 May 2014 20:42:24 GMT

PCT_VAR is a decimal, which results in a percentage after applying the NUM() percent format. I am filtering down to a level in which PCT_VAR is (intentionally) additive.

My question is regarding formatting rather than the variable values themselves. Thanks.

Re: Conditional Number Formatting with Custom Parameter Variable

crusader_ — Fri, 16 May 2014 12:55:15 GMT

Hi Jason,

Whether your vMetric could have 'PCT_VAR' and 'INT_VAR'?

If so, it should be ok (in Expression)

if($(vMetric)='PCT_VAR', num(sum(PCT_VAR),'####.00%'),num(sum(INT_VAR),'#,###'))

Hope this helps.

Andrei