work out growth vs market growth

[louwriet]

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

Thank you

Regards

Louw

[MayilVahanan]

Hi

     Can you say time is datefield?

     If so,

     Try like this,

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

[louwriet]

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

[louwriet]

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

Help would be appreciated.

Regards

Louw