0

I have All_Product hierarchy in All_product dimension. strength-->product-->market

I want calculation for product growth and market growth on monthly basis((current-prev)/prev). I used scripts to calculate product growth (wrt product) and market growth (wrt market) which working fine but how to show for market growth with respect to each product. eg: market growth for each product under that particular market will be same example:

I have market m1 and under market m1 having products p1,p2 and p3. I have individual product growths and market growths. Question is, when I will go to market to product level then market growth value for products p1, p2, p3 should be same.

If product_growth (m1)=50 and market_growth(m1)=75 and product_growth of p1, p2 and p3 may be 15,25,10 but market_growth for p1,p2,p3 will be 75,75,75

Script I am using for market growth:

SCOPE 
  (
    [Monthly Growth].[VOLUME CALC].&[2.]
   ,Descendants
    (
      [All Products].[All Products].[All]
     ,[All Products].[All Products].[Market]
    )
   ,self
  );
  this = 
    IIF
    (
        Aggregate
        (
          [All Time Periods].[All Time Periods].[Prev_Month]
         ,[Monthly Growth].[VOLUME CALC].DefaultMember
        )
      = 0
     ,NULL
     ,
        (
          Aggregate
          (
            [All Time Periods].[All Time Periods].[Current_Month]
           ,[Monthly Growth].[VOLUME CALC].DefaultMember
          )
        - 
          Aggregate
          (
            [All Time Periods].[All Time Periods].[Prev_Month]
           ,[Monthly Growth].[VOLUME CALC].DefaultMember
          )
        )
      / 
        Aggregate
        (
          [All Time Periods].[All Time Periods].[Prev_Month]
         ,[Monthly Growth].[VOLUME CALC].DefaultMember
        )
    );
END SCOPE;

That means same parent values will flow to their own children as same.

halfer
  • 19,824
  • 17
  • 99
  • 186
Anil
  • 39
  • 2
  • @SouravA , can you please give some idea on this? – Anil Nov 25 '15 at 17:04
  • @whytheq , can you please give some idea on this? – Anil Nov 25 '15 at 17:04
  • @mmarie can you please give some idea on this? – Anil Nov 25 '15 at 17:14
  • @ FrankPl, can you please give some idea on this? – Anil Nov 25 '15 at 17:15
  • you need to rephrase it - I cannot speak for the others but I do not understand this question in it's current form. Please try to model in AdvWrks. Also please include screenprints of what results you are getting, why the results are wrong – whytheq Nov 26 '15 at 08:11
  • @whytheq In a heirarchy, perticular parent member data will flow to its child members. if parent p1 has children c1, c2, c3 and market growth for p1 is 50 then market growths for c1, c2, c3 would be 50, 50 ,50 as c1,c2,c3 belongs to parent p1. – Anil Nov 26 '15 at 10:38
  • so that is the desired behaviour - what is the current behaviour? Can you model in the AdvWrks cube? – whytheq Nov 26 '15 at 11:50
  • @whytheq now for children c1, c2,c3 for parent p1 I am getting different values for market growth. Because I am using script scope ...and heirachy is market---->product ....for market growth scope is only for markets..that's why for products going out of scope....you can check my above script....market growth is (current month - prev month)/prev month – Anil Nov 26 '15 at 17:09

0 Answers0