6 Replies Latest reply: Oct 30, 2012 3:32 AM by Robin Harris RSS

    How calculate Avg Based On Month number

    962252
      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
          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
            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
              Did you dry my solution? You suppose use your month column in given code.
              • 4. Re: How calculate Avg Based On Month number
                Robin Harris
                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
                  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
                    Robin Harris
                    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