Conditional Number Formatting with Custom Parameter Variable

[#1]

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!

[clisboa_noesis]

Hi,

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

Best Regards,

Carlos

[crusader_]

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

[#4]

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

[#5]

Hi Carlos - yes Expression Default

[clisboa_noesis]

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

[#7]

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.

[crusader_]

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