Author Topic: IF THEN ELSE not working  (Read 547 times)

Offline AJAYC

  • Senior Member
  • ****
  • Join Date: Apr 2012
  • Posts: 53
  • Forum Citizenship: +2/-0
IF THEN ELSE not working
« on: 19 Jun 2017 04:56:46 am »
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

Offline Mimo91

  • Full Member
  • ***
  • Join Date: Dec 2016
  • Posts: 10
  • Forum Citizenship: +0/-0
Re: IF THEN ELSE not working
« Reply #1 on: 19 Jun 2017 08:18:45 am »
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.

Offline AJAYC

  • Senior Member
  • ****
  • Join Date: Apr 2012
  • Posts: 53
  • Forum Citizenship: +2/-0
Re: IF THEN ELSE not working
« Reply #2 on: 19 Jun 2017 10:16:42 am »
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

Offline nithya1224

  • Statesman
  • ******
  • Join Date: Apr 2014
  • Posts: 520
  • Forum Citizenship: +1/-1
Re: IF THEN ELSE not working
« Reply #3 on: 19 Jun 2017 02:22:04 pm »
try this

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

Thanks,
Nithya

Offline AJAYC

  • Senior Member
  • ****
  • Join Date: Apr 2012
  • Posts: 53
  • Forum Citizenship: +2/-0
Re: IF THEN ELSE not working
« Reply #4 on: 19 Jun 2017 03:16:59 pm »
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 :(

Offline AJAYC

  • Senior Member
  • ****
  • Join Date: Apr 2012
  • Posts: 53
  • Forum Citizenship: +2/-0
SOLVED>>>Re: IF THEN ELSE not working
« Reply #5 on: 20 Jun 2017 06:34:07 am »
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

Offline yvswamy

  • Full Member
  • ***
  • Join Date: Jul 2012
  • Posts: 11
  • Forum Citizenship: +0/-0
Re: IF THEN ELSE not working
« Reply #6 on: 22 Jun 2017 05:24:10 am »
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.

 


       
Twittear