SAP Drives me crazy!
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.
"To frustrated to write actual code"
IF Last character of string ="-"
Remove last character and convert to number then * -1
Else
convert string to number.
SAP :X
- 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
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
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
- 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
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:
Hope that helps.
Regards,
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 ));
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!
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!
- 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
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.
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
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
- 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
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,
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!
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!