TM1 rule subst with dimension element

Post Reply
viocristi
Posts: 4
Joined: Wed Nov 27, 2019 1:52 pm
OLAP Product: Planning Analytics
Version: 2.0.6
Excel Version: 365

TM1 rule subst with dimension element

Post by viocristi » Thu Nov 28, 2019 9:40 am

Hi there,

I would need some help with the correct syntax.

I have a dimension, let's call it abc. This dimension has the following structure:
Total abc
all internal abc
total internal
1000
1100
total external
2000
2100
2000
3000
3100

What I want to achieve: in the cell security of a cube, I want to edit the existing rule to add one condition to simply ignore all elements of the dimension which start with a 2.

I tried: & subst(!abc,1,1) @<> '2'
I get: Syntax error on or before:'' !abc,1,1) @ <> '2' Invalid string expression.

How can I fix this?
Thank you

tomok
MVP
Posts: 2642
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1 rule subst with dimension element

Post by tomok » Thu Nov 28, 2019 12:13 pm

To get help you're going to need to provide the actual offending code instead of pseudo code.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

viocristi
Posts: 4
Joined: Wed Nov 27, 2019 1:52 pm
OLAP Product: Planning Analytics
Version: 2.0.6
Excel Version: 365

Re: TM1 rule subst with dimension element

Post by viocristi » Thu Nov 28, 2019 12:50 pm

It's really the same case. The only difference is that I have another 3 letters as my dimension name. The numbers and the structure and the formula I used is the same.

I just want an example of how would one would exclude all the dimension elements which start with a certain character. I know that i can call the dimension element by using !dimName and the subst function should allow me to do a LEFT 1 on that string and then <> with the character I want to ignore.
Last edited by viocristi on Thu Nov 28, 2019 12:55 pm, edited 1 time in total.

tomok
MVP
Posts: 2642
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1 rule subst with dimension element

Post by tomok » Thu Nov 28, 2019 12:54 pm

viocristi wrote:
Thu Nov 28, 2019 12:50 pm
It's really the same case. The only difference is that I have another 3 letters as my dimension name. The numbers and the structure and the formula I used is the same.
Really? No one is going to be able to help you diagnose a syntax error without seeing the exact offending code. I guess you really don't want anyone's help. :oops:
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

viocristi
Posts: 4
Joined: Wed Nov 27, 2019 1:52 pm
OLAP Product: Planning Analytics
Version: 2.0.6
Excel Version: 365

Re: TM1 rule subst with dimension element

Post by viocristi » Thu Nov 28, 2019 12:59 pm

please see my edit: I would be happy with just a general example on how to filter out an element.
Something of the equivalent of an sql syntax of:

and left (abc,1) != '2'

tomok
MVP
Posts: 2642
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: TM1 rule subst with dimension element

Post by tomok » Thu Nov 28, 2019 2:54 pm

viocristi wrote:
Thu Nov 28, 2019 12:59 pm
please see my edit: I would be happy with just a general example on how to filter out an element.
Something of the equivalent of an sql syntax of:

and left (abc,1) != '2'
SUBST(!abc,1,1) @= '2'
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

viocristi
Posts: 4
Joined: Wed Nov 27, 2019 1:52 pm
OLAP Product: Planning Analytics
Version: 2.0.6
Excel Version: 365

Re: TM1 rule subst with dimension element

Post by viocristi » Thu Nov 28, 2019 3:32 pm

so my syntax was ok, I can do something like that in a tm1 rule, so it must be something else. Thank you

MGrain
Posts: 15
Joined: Wed Nov 15, 2017 11:36 am
OLAP Product: TM1
Version: Various
Excel Version: Various

Re: TM1 rule subst with dimension element

Post by MGrain » Mon Dec 02, 2019 1:38 pm

Sometimes it can help to wrap up your element reference in something else:

Subst(DIMNM('abc',DIMIX('abc',!abc)),1,1)@<>'2'

User avatar
Steve Rowe
Site Admin
Posts: 1965
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: TM1 rule subst with dimension element

Post by Steve Rowe » Tue Dec 03, 2019 9:46 am

I have seen issues with the compiler where if you mix a numeric test and a string test in the same conditional expression that it will give a false positive syntax error, it was a long time ago though.

i.e. If ( 1=2 & 'a'@='b' ,.... would fail to compile but If ( 'a'@='b' & 1=2 , ........... would

Post Reply