IF THEN ELSE not working

[AJAYC]

Hi

I have an issue with "IF THEN ELSE" statement not working for me using a dimensional model.

Example

I am trying to test a prompt value and derive another member from it, so

IF
([CubeName].[Dimension].[Dimension]->?PromptMonth? ) = 'Oct' )
THEN
([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Oct YTD])
ELSE
([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Full Year^Opening Balance]


I have tried a number of variations of this, including trying to include caption statements, and member statements but none with success.

I keep getting a combination of syntax errors, or no data being displayed.

Has anyone managed to get this working when using TM1 cubes as a source ?

Thanks
Ajay

[Mimo91]

Hi Ajay,

I am seeing that there is syntax error in your code (bracket mismatch). It should be;

IF
([CubeName].[Dimension].[Dimension]->?PromptMonth? = 'Oct')
THEN
([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Oct YTD])
ELSE
([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Full Year^Opening Balance])

If it does not work you can try;

(([CubeName].[Dimension].[Dimension]->?PromptMonth? = 'Oct') AND ([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Oct YTD])) OR (([CubeName].[Dimension].[Dimension]->?PromptMonth? <> 'Oct') AND ([CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Full Year^Opening Balance]))

Thanks.

[AJAYC]

Thanks Mimo91

Unfortunately no luck. I corrected the code as advised:

IF
   ( [CubeName].[Dimension].[Dimension]->?PromptMonth? = 'Oct'  )
THEN
   ( [CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Oct YTD] )
ELSE
   ( [CubeName].[Dimension].[Dimension]->:[TM].[Dimension].[Dimension].[@MEMBER].[Full Year^Opening Balance] )


What I have noticed is that the condition fails to satisfy, as per the attachment, the data item for the crosstab, does pickup the "Opening Balance" however no data is returned......not sure why it doesn't allow the prompted value of "Oct" to equal 'Oct' in the report expression above.

Ajay

[cognos05]

try this

if(?prmCountry?='China')
Then
([Revenue])
Else
([Quantity sold])

Thanks,
Nithya

[AJAYC]

Hi Nithya

Thanks for your suggestion. I followed your code with the following version, but unfortunately it still didn't work.

IF
(?PromptMonth?='Oct')
THEN
([Oct YTD])
ELSE
([Opening Balance])


Regards
Ajay :(

[AJAYC]

Hi Guys

I didn't get any luck with the "IF...THEN...ELSE" so I made changes TM1 side to accomplish what I needed instead.

I created an alternate hierarchy within the dimension which looks similar to this:

All Periods
--Oct
--Oct YTD
--Nov
--Nov YTD
etc


Then, I changed the report expression for the data item replacing the "Full Year" where my prompt is based to "All Periods", as below:

lead (
         #Substitute ( 'Full Year', 'All Periods', prompt ( 'PromptMonth', 'memberuniquename' ))#,
        1)



Hope this helps anyone else stuck on a similar issue. I am still concerned as to why the "IF..THEN..ELSE" failed to work

Thanks for your help

Ajay

[yvswamy]

I believe you are working with Slicers.

Slicers won't accept IF ELSE or AND OR statements.  You need to use macros to get what is required for you.