Number Formatting in the Charts

[colinodonnel]

Hello there,

Is this a bug or is there something else?

The number format for all 5 row within this Bar chart is Auto.

The expressions for 4 rows is wrapped by an If Statement. The expression for the middle row is not.

However when I wrap the middle row by an If Statement, then the format changes to match the others, which is desired,

Why is this?

Thank you,

Colin

EDIT: Noticed something further regarding the If statement which might help.

If( formula > 0, formula, 0) -> the number is automatically abbreviated as required.

If( formula > 0, formula, null()) -> the number is not abbreviated as required.

The presence of the null() prevents the number from being automatically abbreviated.

If there anyway around this except to remove null() from the If statements??

Note: the formula does not have null() explicitly referenced in it, however there are Nulls in the underlying dataset.

[marcus_sommer]

NULL means nothing - it does not exists - and therefore no calculation or formatting could be applied. I'm not absolutely sure but I think that your expression showed in the end a numeric value but it is the result of multiple row-level calculations - if one of this row-results is null() then it changed the result-type from numeric to mix and the autoformatting won't be applied.

Why do you want to use null() there, any special reason? I could imagine to use the following as expression:

rangemax(formula, 0)

- Marcus

[colinodonnel]

Hello Marcus,

Thank you for the reply.

The NULL isn't there for any specific reason and it's not intentional.  It was just used instead of Zero without much thought. Wasn't aware that of the formatting consequence.

I think you are right "if one of this row-results is null() then it changed the result-type from numeric to mix and the autoformatting won't be applied"

rangemax(formula, 0) doesn't work. Thought it would as Rangesum handles NULL unlike a simple NULL + 0 which is NULL.  couldn't seem to track down how to format it back to numeric (num doesn't work either)

So it seems:

1 -  sticking with the If statement  or

2 -  cleanse the data and replace NULL with Zero where applicable.

?

Thanks

[marcus_sommer · Accepted solution]

Then I think keeping the if-loop would be the most practically approach. If there are no further issues I wouldn't invest time to find the real reason or clean the data or developing any other bypass-approach.

- Marcus