Can IF statements be used in a SUM Chart Expression ?

[#1]

Hi,

I am trying to create a SUM expression in a Chart that needs to sum different columns depending on the current field selection.

Can someone tell me the proper syntax.

I have 3 fact columns in my database ( SalesMarket, SalesP1 & SalesP2 )

In my Chart Object, I have a group Products that cycles between the levels(aka Fields) "Market", "P1" & "P2".

What I need to do is :

• If [Products] group is displaying "Market" then sum the SalesMarket
• If "P1" then sum SalesP1
• If "P2" then sum SalesP2
• otherwise 0

Tried this but it does not return anything

SUM ( IF(getcurrentfield([Products]) = "Market" then SalesMarket elseif getcurrentfield([Products]) = "P1" then SalesP1 elseif getcurrentfield([Products]) = "P2" then SalesP2 else 0 end if )

Thanks for you help

Aubrey

Tested that the getcurrentfield([Products]) sytnax is correct (Using a table object )

[jpapador]

Try:

SUM (IF(getcurrentfield([Products]) = 'Market', SalesMarket, If(getcurrentfield([Products]) = 'P1', SalesP1, If(getcurrentfield([Products]) = 'P2', SalesP2, 0))))

[Nicole-Smith · Accepted solution]

sum(pick(match(GetCurrentField([Product]),'Market','P1','P2'),SalesMarket,SalesP1,SalesP2))

[#4]

Thanks J

It looks like it should work but it is returning 0s. Will look at the whole db again.

Aubrey

[#5]

Thanks Nicole,

It looks like the pick(Match( should work but it is returning 0s. I will look at the whole db again.

Aubrey

[jpapador]

What you could also do is create an expression for each value of the cycle.  then in the conditional box use your GetCurrentField([Product]) command.  That way when you cycle the dimension 1 expression will be hidden and the next one will show up.  Then you would not need an if statement.

[#7]

Your solution worked great !

Turns out it was my mistake, I was using double quotes ( "Market" ) instead of the single quotes.

Thank you

Aubrey

[#8]

Thanks J,

Your answer is right. I was incorrectly using double quotes instead on single quotes.

Aubrey