3 Replies Latest reply: Aug 1, 2012 4:26 PM by Louwrie Terblanche RSS

    work out growth vs market growth

    Louwrie Terblanche

      Hi ,

       

      I would appreciate help with the following.

       

      I need to calculate
      the growth of products against the average growth of the amount of products
      that has values both for this year MAT and the preious YEAR MAT.  I NEED TO IGNORE PRODUCTS WHO DOESN’T HAVE
      VALUES IN BOTH OR EITHER IN ONE.

       

      I have total sales figures for products on MAT for 2 years.
      First I calculate the growth per product against the previous year’s MATS.

       

      Then I want to calculate the AVERAGE growth of the products and compare each product’s growth
      against the AVERAGE growth.

       

      This is my expressions:

       

       

      Month 4 2011 :    sum(if (Inmonths (1,Time, SetDateYear (MonthAprilPrev, max (total Year (Time))-1), 0), Value))    THE VARAIBLE MonthAprilPrev is 01/04/2011

       

      Month 4 2012 :  sum(if (Inmonths (1,Time, SetDateYear (MonthApril, max (total Year (Time))), 0), Value)) THE VARAIBLE
      MonthApril is 01/04/2012

       

      Growth Month 4 2012 :  (sum(if
      (
      Inmonths (1,Time,SetDateYear (MonthApril,max (total Year
      (
      Time))), 0), Value))/sum(if
      (
      Inmonths (1,Time,SetDateYear (MonthAprilPrev,max (total Year
      (
      Time))-1), 0), Value)))-1

       

      Ave Growth Month 4
      2012:
      (I have just added TOTAL to the
      expression)

       

       

      ( sum(TOTAL if (Inmonths (1,Time, SetDateYear (MonthApril, max (total Year (Time))), 0), Value)) /  sum(TOTAL if (Inmonths (1,Time, SetDateYear (MonthAprilPrev, max (total Year (Time))-1), 0), Value)) ) -1

       

      This AVERAGE growth is not workingas you can see on screenshot: what I need to do is to sum the growth of
      products that has values in MAT 2011 and MAT 2012 AND THEN I NEED TO DIVIDE THS
      TOTAL INTO THE AMOUNT OF PRODUCTS THAT HAVE VALUES IN BOTH MAT 2011 AND MAT
      2012

       

      Then I can work out

       

      Growth
      Month 4 VS Market Growth 2012:

       

      ((sum(if (Inmonths (1,Time, SetDateYear ((MonthApril)-0, max (total Year (Time))), 0), Value))/sum(if (Inmonths (1,Time, SetDateYear ((MonthAprilPrev)-0, max (total Year (Time))-1), 0), Value)))-1)

      -

        (( sum(TOTAL if (Inmonths (1,Time, SetDateYear ((MonthApril)-0 , max (total Year (Time))), 0), Value)) /  sum(TOTAL if (Inmonths (1,Time, SetDateYear ((MonthApril)-0, max (total Year (Time))-1), 0), Value)) ) -1)

       

      The yellow marked figure is the actual expected results

      AVEPIC2.PNG

       

      Thank you

       

      Regards

       

      Louw

        • Re: work out growth vs market growth
          mayilvahanan ramasamy

          Hi

           

               Can you say time is datefield?

           

               If so,

           

               Try like this,

           

               =Sum({<Time = {$(MonthApril)}>}value) but make sure, both are in same format

            • Re: work out growth vs market growth
              Louwrie Terblanche

              Thank you for the reply Mayil, my problem is actually column E the average growth - how to calculate that.

              I must take all the growth percentages in column D and get a average growth rate.

               

              I also need to ignore in the calculation if a product has no value in B and C

               

              My idea was to sum the growth (columnD) of products that has values in both columns B and C and then to devide the result with the amount of products that has values. Hope this explain it better.

               

               

               

              Regards

               

              Louw

                • Re: work out growth vs market growth
                  Louwrie Terblanche

                  To describe the Ave problem i have see excample below where i need to calculate the ave selling price for a product in a range of shops.

                   

                  I have picked up this expression in a thread but is have the problem now that the average of the products is calculated for all products instead of  ave selling price per product in the group of shops.

                   

                  avg(TOTAL aggr(sum({$<Sales={"=LEN(Sales)>0"} >}Sales)/sum( {$<Quantity={"=LEN(Quantity)>0"} >} Quantity), shop))  What i need is the average selling price per product in the shops

                   

                   

                  shot1.png

                  Help would be appreciated.

                   

                  Regards

                   

                  Louw