SAP :X

Post Reply
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

SAP :X

Post by Eric »

SAP Drives me crazy! :x

Anyone deal with a system sending negative number fields as "1-". It obviously errors on TI. Any tips or tricks or do i need do the following.

:evil: "To frustrated to write actual code" :evil:

IF Last character of string ="-"
Remove last character and convert to number then * -1
Else
convert string to number.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: SAP :X

Post by Mike Cowie »

Eric,

I don't know of a natural way to make TI understand such a number format - maybe someone else does. I think you're stuck with a formula here. So, let's say you have a variable called "Amount" and Amount = 12345.67-

To make this work properly and avoid annoying errors, you'll need to make the Amount variable a String type in TI.

Then in a custom variable, let's call it "ConvertedAmount" (make it Numeric TI variable type) you could have a variable like the following:

Code: Select all

ConvertedAmount = IF( SUBST( Amount, LONG( Amount ), 1 ) @= '-', -NUMBR( SUBST( Amount, 1, LONG( Amount ) - 1 ) ), NUMBR( Amount )); 
Hope that helps.

Regards,
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: SAP :X

Post by Eric »

That is what I thought (fear).

Thanks for suggesting 2 variables in self loathing I might have overlooked that and caused more headaches for myself.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
User avatar
Mike Cowie
Site Admin
Posts: 482
Joined: Sun May 11, 2008 7:07 pm
OLAP Product: IBM TM1/PA, SSAS, and more
Version: Anything thru 11.x
Excel Version: 2003 - Office 365
Location: Alabama, USA
Contact:

Re: SAP :X

Post by Mike Cowie »

Eric,

There may also be some things you or someone can do to the data source to get values in a more readily consumed format, but in my experience getting someone to do work on any SAP like modify an ABAP report doesn't happen quickly. Good luck.

Regards,
Mike Cowie
QueBIT Consulting, LLC

Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
Post Reply