Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Conditional Number Formatting with Custom Parameter Variable

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!

7 Replies
Partner
Partner

Re: Conditional Number Formatting with Custom Parameter Variable

Hi,

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

QlikView - Remote Desktop Connection Manager v2.2_2014-05-15_10-27-49.png

Best Regards,

Carlos

Partner
Partner

Re: Conditional Number Formatting with Custom Parameter Variable

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

Not applicable

Re: Re: Conditional Number Formatting with Custom Parameter Variable

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

PIDPersonINT_VARPCT_VAR

1

Mary157123.2342
1Collin415612.3445
1Camaro233345.6754
2Mary16664.2312
2Camaro1234566.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

Not applicable

Re: Re: Conditional Number Formatting with Custom Parameter Variable

Hi Carlos - yes Expression Default

Highlighted
Partner
Partner

Re: Re: Conditional Number Formatting with Custom Parameter Variable

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

Not applicable

Re: Re: Conditional Number Formatting with Custom Parameter Variable

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.

Partner
Partner

Re: Conditional Number Formatting with Custom Parameter Variable

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