Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 213 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Help with added column in obiee 11g
Hi
The subject area I am working with does not have quota so I need to create a new column in my analysis and have the quota in it so I have created a case statement to say that if region is NAMER then 1000000 when region is EMEA then 2000000 etc and that will display a number beside each region if I have 2 columns analysis the region and quota...now I need to add a column from the subject area which is the spend so now I have 3 columns region, quota (made by case statement) and spend...I need to create a 4th column myself (not from the SA) to show the attainment which is Spend *100 / the case statement I built and now I am getting an error.
Here is my formula (my case statement in here is only for NAMER just to test it)
"Fact - Customer AR Revenue"."Amount USD"*100/ CASE WHEN "Ship Customer Country Parent Geo"."Ship Customer Country Parent Region" = 'NAMER' THEN '10000000' END
Here is my error
Formula syntax is invalid.
[nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 22023] An arithmetic operation is being carried out on a non-numeric type. Please have your System Administrator look at the log for more details on this error. (HY000)
SQL Issued: SELECT "Fact - Customer AR Revenue"."Amount USD"/"Fact - Customer AR Revenue"."Amount USD"*100/ CASE WHEN "Ship Customer Country Parent Geo"."Ship Customer Country Parent Region" = 'NAMER' THEN '10000000' END FROM "Customer Spend"
Any idea on how to do that?
Thanks
Joe
Answers
-
Joe,
'10000000' is a string - equivalent to 'blue' or 'banana' or 'slartibartfast'. Not sure what the mathematical result of a 50000 / 'telephone' should be with that formula.
Maybe read up on what a data type is:
0 -
Once you went through data types and fixed that part I can already tell you the follow up question you will have: why is the result of the calculation of your 4th column not right?
The good news is that the result is actually right.
You will maybe want to look for "integer division" vs floating point division: https://gerardnico.com/dat/obiee/int#division
0 -
LOL X-D
You just had to point that out didn't you
0 -
99% chances the question was coming right after, so I provide a reply before he even has to ask
0 -
Not saying you're wrong at all. Just funny.
0 -
Thank you both- I had an idea that I needed to convert the string to number but was not sure how and now I guess I need to read more and more about the CAST function in OBIEE. Thanks again.
Joe
0
