This content has been marked as final. Show 6 replies
Use this formula1 person found this helpful
for better results I would suggest to go by
Pls mark as correct.
I have month column values Jan,feb,mar.....like That.
How to convert To Month Number.
Ex:my requ like this
My Report Is running In March
jan 10 10/3
Feb 15 15/3
March 24 24/3
Same Report Running In may motny
jan 10 10/5
feb 15 15/5
march 24 24/5
Appril 70 70/5
May 100 100/5
Did you dry my solution? You suppose use your month column in given code.
Can you use the CURRENT_MONTH repository variable?
This is the month in YYYY / MM format so you can substring this to get the month number.
For example dividing the Approved PO Quantity by the current month number would be:
"Fact"."Approved PO Quantity" / CAST(SUBSTRING(VALUEOF("CURRENT_MONTH") FROM 8 FOR 2) AS int)
So all you would need to do is substitute your fact column for "Fact"."Approved PO Quantity"
Please mark as helpful/answered.
Edited by: Robin Harris on 26-Oct-2012 04:22
When I useing this Query getting Syntrax Error.
Please Let me know where can i mistake.
Total/CAST(SUBSTRING(VALUEOF("CURRENT_MONTH") FROM "Time"."Month" FOR 2)AS INT)
Error like this
Formula syntax is invalid.
[nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 22019] Function Subtring (argument 2) does not support non-numeric types. (HY000)
SQL Issued: SELECT CAST(SUBSTRING(VALUEOF("CURRENT_MONTH") FROM "Time"."Month" FOR 2)AS INT) FROM "Financial Accounts and Applications".
I will Check Current_month repository varible is working Fine.
You need to specify a numeric for the 2nd parameter. The Substring function takes the parameters of source string, starting character and length of sub section. So in my example it will take the value of current_month variable and return the characters from the 8th postion for 2 characters (so character 8 and 9). So instead of passing "Time"."Month" you need to pass the literal number 8. So I think the actual code you need is:
SELECT "Financial Accounts and Applications"."Value" / CAST(SUBSTRING(VALUEOF("CURRENT_MONTH") FROM 8 FOR 2)AS INT) FROM "Financial Accounts and Applications"
NB I am assuming the value you want to divide by the current month number is called "Financial Accounts and Applications"."Value", please change this to be what ever column you need to use.
Please mark as useful/answered