Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
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 )
sum(pick(match(GetCurrentField([Product]),'Market','P1','P2'),SalesMarket,SalesP1,SalesP2))
Try:
SUM (IF(getcurrentfield([Products]) = 'Market', SalesMarket, If(getcurrentfield([Products]) = 'P1', SalesP1, If(getcurrentfield([Products]) = 'P2', SalesP2, 0))))
sum(pick(match(GetCurrentField([Product]),'Market','P1','P2'),SalesMarket,SalesP1,SalesP2))
Thanks J
It looks like it should work but it is returning 0s. Will look at the whole db again.
Aubrey
Thanks Nicole,
It looks like the pick(Match( should work but it is returning 0s. I will look at the whole db again.
Aubrey
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.
Your solution worked great !
Turns out it was my mistake, I was using double quotes ( "Market" ) instead of the single quotes.
Thank you
Aubrey
Thanks J,
Your answer is right. I was incorrectly using double quotes instead on single quotes.
Aubrey