This discussion is archived
6 Replies Latest reply: Oct 30, 2012 1:32 AM by rharrispcl

# How calculate Avg Based On Month number

Currently Being Moderated
Hi All,

I want Calculate Avg based on month means

Ex:i have Total Fact column
present month like July Thn avg=total/7.
then same report working in Octber that time avg=total/10.

Any Help.

thanks,
• ###### 1. Re: How calculate Avg Based On Month number
Currently Being Moderated
Use this formula
Total/(max(rcount(month))

for better results I would suggest to go by
(Total*1.00)/(max(rcount(month))

Pls mark as correct.
• ###### 2. Re: How calculate Avg Based On Month number
Currently Being Moderated
Hi,

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

like This......
• ###### 3. Re: How calculate Avg Based On Month number
Currently Being Moderated
Did you dry my solution? You suppose use your month column in given code.
• ###### 4. Re: How calculate Avg Based On Month number
Currently Being Moderated
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"

Edited by: Robin Harris on 26-Oct-2012 04:22
• ###### 5. Re: How calculate Avg Based On Month number
Currently Being Moderated
Hi ,

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.
• ###### 6. Re: How calculate Avg Based On Month number
Currently Being Moderated
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.