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

How calculate Avg Based On Month number

962252 Newbie
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
    Srini VEERAVALLI Guru
    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
    962252 Newbie
    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
    Srini VEERAVALLI Guru
    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
    rharrispcl Explorer
    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"

    Please mark as helpful/answered.

    Edited by: Robin Harris on 26-Oct-2012 04:22
  • 5. Re: How calculate Avg Based On Month number
    962252 Newbie
    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
    rharrispcl Explorer
    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.


    Please mark as useful/answered

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points