9 Replies Latest reply: Jan 14, 2013 3:40 PM by Erica Whalley RSS

    Averages

      I am trying to write an expression that extrapolates a period of data over a full year e.g. (sales /current number of months)x 12. I am struggling to do this so when I select a month the expression automatically changes e.g. August 2012 is selected the calculation would be (August 2012 YTD sales/8)x12.

        • Re: Averages

          Hi Tom

           

          In what format is your month data stored? If it is a date field, you could use the Month() function to get the number of the month to use in the denominator.

           

          Otherwise, you will need to turn the text field containing the month back into a date using the date#() function, then use the month() around it.

           

          eg =month(date#('August 2012','MMMM YYYY'))*1 returns '8'

           

          Do let me know if this helps,

           

          Erica

          • Re: Averages
            Kabilan Kumarasamy

            If u have a date field then use below logic

             

            Sales=Sum(Sales)

            current number of months=month(max(Datefield))-month(min(Datefield))

             

            ~Kabilan K

              • Re: Averages
                Kabilan Kumarasamy

                Hi,

                 

                =month(max(Datefield))-month(min(Datefield))   // it won't return correct value for some scenario(Month('01/08/2013')-month('03/21/2012')

                 

                So, I have changed the expression like below

                 

                current number of months=ceil((MonthName(max(Datefield))-MonthName(min(Datefield)))/30)

                 

                 

                ~Kabilan K