Conditional Number Formatting in Expression

[#1]

Hi all,

i am facing a problem in formatting the number with different format according to different conditions in the Expression.

Based on the attached example:

There are columns named Revenue and Gross Margin in a Straight Table. I would like to display the value with different formatting according to different [Figure Type].

For example, when [Figure Type]='YTD Plan' or 'YTD Actual', the display format of [Revenue] and /[Gross Margin] should be integer.

When [Figure Type]='YTD(%)', the value should be in percentage with 2 decimal place.

The problem i am facing now is that when i select months in ListBox, the number display wrong formatting and wrong value.

But they show correctly if no month is selected (I already used Set Analysis in Expression).

The Expression is shown as below:

if([Figure Type]<>'YTD(%)' and [Figure Type]<>'AL(%)', num(sum({$<Month= >}Revenue),'#,##0'),num(sum({$<Month= >}Revenue),'#00.0%'))

Could anyone help me to check anything wrong in my expression or kindly give any advice. Thanks a lot!!!

[Gysbert_Wassenaar · Accepted solution]

I don't think there's anything wrong with your expression. This looks like a bug to me. If you're using QV11 there's a workaround that may work in some situations. In QV11 you can create an alternate state (on the General tab of the document properties window) and then assign that alternate state to the straight chart (on the General tab of the charts properties window). In you expression you're using sum({$<Month= >}Revenue). The $ means that it will still 'listen' to selections in the default state, except for Month which will 'listen' to selections in the alternate state. It appears that since the Month listbox is still tied to the default state any selections in it now don't influence the number format any more. See attached example.

[#3]

Hi Gysbert,

Awsome!! I think i need to learn more Alternative State.

Anyway, many thanks!

Regards.