Formatting issue in a nested if

[helen_pip]

Hello

I have written the following expression, which when used within a pivot table, the formatting works perfectly

If(v_Selected=1, Num(Count({$<FactDateNum = {">=$(=v_Start_Date_IP)<=$(=v_End_Date_IP)"}>}IP_Spell_No),'#,###'),

If(v_Selected=2, Num(avg({$<FactDateNum = {">=$(=v_Start_Date_IP)<=$(=v_End_Date_IP)"}>}IP_Spell_LOS), '#,##0.00'),

If(v_Selected=3, Num(avg({$<FactDateNum = {">=$(=v_Start_Date_IP)<=$(=v_End_Date_IP)"}>}IP_PreOP_LOS), '#,##0.00') ,

If(v_Selected=4, Num(avg({$<FactDateNum = {">=$(=v_Start_Date_IP)<=$(=v_End_Date_IP)"}>}IP_PostOP_Los),'#,##0.00')

))))

When I store the expression in my expression spreadsheet and load it into my QVW to use as a variable, the formatting does not seem to work anymore

$(v_IP_Ave_LOS_Count_Spells_Nested_If)

Can anyone help me with why the expression does not work when stored and used as a variable

Kind Regards

Helen

[sunny_talwar]

Do you see a '=' sign next to the variable definition in variable overview? You might need that if that is missing I think

[jonathandienst]

Does the expression not work at all, or is it just not applying the formats correctly? If the latter, what results are you getting and what do you expect to get?

[helen_pip]

Hello Jonathan

Thank you for your response.  When using the variable, I am getting values such as:

$3e+005$$$ but when I use the expression on its own I am getting the desired values I.e. 2,345

Do you think formatting may be the issue?

Kind Regards

Helen

[jonathandienst]

The $ signs are a little peculiar, but it seems that the formatting is 'lost' when the variable expands, although I can't say why. I suggest that you experiment with the format settings on the Number tab of the chart/table properties. Do you have "expression default" checked for this expression?

[jonathandienst]

You might also want to try the expression in a new straight/pivot table with the same dimensions, just to make sure that it is not corruption of the expression in the existing table.

[helen_pip]

Hi There

I have tried the expression in a new pivot table and get the same problem

The pivot table is set to Expression Default when the problem occurs, but if I change it to an integer or Fixed decimals, I get values again.   I would like to just change the formatting on the number tab, but as 1 of the if statements requires a different formatting from the others, I can not set them all to the same format

Thank you for your suggestions.  For now, I have referred back to my original expression and am not using the variable at the present time

Kind Regards

Helen

[Anonymous]

Hi helen_pip,

Try this on your var or in your chart:

=Pick(Selected,

     Num(Count({$<FactDateNum = {">=$(=v_Start_Date_IP)<=$(=v_End_Date_IP)"}>}IP_Spell_No),'#,##0','.',','),

     Num(avg({$<FactDateNum = {">=$(=v_Start_Date_IP)<=$(=v_End_Date_IP)"}>}IP_Spell_LOS), '#,##0.00','.',','),

     Num(avg({$<FactDateNum = {">=$(=v_Start_Date_IP)<=$(=v_End_Date_IP)"}>}IP_PreOP_LOS), '#,##0.00','.',','),

     Num(avg({$<FactDateNum = {">=$(=v_Start_Date_IP)<=$(=v_End_Date_IP)"}>}IP_PostOP_Los),'#,##0.00','.',',')

)

Regards