Register

Contributor

Trim a node name till special character "-"

Hi Team,

Just looking for formula to trim my node name till a special character "-".

'AccountL1- 234'

I want to get only AccountL1.

Regards,

Usha

10 REPLIES 10
Moderator

Re: Trim a node name till special character "-"

Assumption - Account L1 is your list, if not then tweak your second parameter

First you will have to figure out the place of the " -" . Use this formula in Line item Test

Test = FIND("-", NAME(ITEM('Account L1')), 0)

Second line item Test 1 = LEFT(NAME(ITEM('Account L1')),Test-1)

Hope this helps

Misbah

Contributor

Re: Trim a node name till special character "-"

Hi Mishab,

Thanks for your rsponse, but the first formula for test

FIND("-", NAME(ITEM('Account L1')), 0) doesn't give any result and its a numbered list dimension.

the formula worked but doesnt give any value here.

Regards

Usha

Certified Master Anaplanner

Re: Trim a node name till special character "-"

I hope it should be the code of that list. Please try Code(Item('')) instead Name(Item('')).

Regards,
Kavin.
Contributor

Re: Trim a node name till special character "-"

Hi Kavin,

Yes even I thought so, but still no result:(

It takes the formula in but result is 0 every time, though I have special characters in member name.

Thanks
Usha
Certified Master Anaplanner

Re: Trim a node name till special character "-"

Could you please tell us how would you get the result 'AccountL1- 234'? If it is not code/name, then it should be the attribute of that list (Either property/line item). So apply your formula on that property/line item and you will get your end result.

For ex,

I have a numbered list 'A' with Display Name as property(text). Then the formula should be,

Test = FIND("-", 'A.Display name', 0)

Second line item Test 1 = LEFT('A.Display name',Test-1)

Hope this helps.

Regards,
Kavin.

Regards,
Kavin.
Master Anaplanner/Community Boss

Re: Trim a node name till special character "-"

I would think the FIND formula would need to be

FIND("-", 'Account L1'.Display Name, 0)

David

Contributor

Re: Trim a node name till special character "-"

Hi David,

Thanks this now works for child node in AccountL1 dimension perfectly.

Regards
Usha

Contributor

Re: Trim a node name till special character "-"

Hi David,

This is working good for Child Level, but Parent Level for 'AccountL1' is 'AccountL0' which is not a number list, for that the value is showing zero.
Is there any way how we can get the parent level also populated here?

Thanks
Usha
Master Anaplanner/Community Boss

Re: Trim a node name till special character "-"

The challenge you have here is the line item is dimensioned by Account L1 and in a composite hierarchy Account L0 is a calculation

So we need to build a couple of extra line items

1. You need to know if the item is a L1 or L0, so set up a Boolean line item called Child? and set this to TRUE

2. Create another line item that brings in the Parent of the L1 - in your model it will be something like PARENT((ITEM('Account L1')).  It is always best to have this in a separate line item (You will likely use it again).  Remember "calculate once, reference many times"

3. Create a text line item for the name of A0, but set the summary option to 'Last non-blank'

4. This is the existing line item we just solved for

5. Now you bring it all together with a conditional and set this summary option to 'Formula'

Viola!

David