1 Reply Latest reply: Apr 16, 2014 6:31 PM by Dariusz Mielczarek RSS

    Aggregate Average

    Isabel Nowinowski

      Hello all,

                  I have a question regarding an aggregate Average. I am trying to determine the average net units for the last 52 weeks. However, I only want to average the weeks where the total numbers of stores that sold that particular SKU is atleast half of the maximum number of stores that sold the SKU in a particular week.

       

      What that means it that I want to determine the week with the maximum number of stores selling the particular SKU. I then only want to include the weeks in my average where the total number of stores selling that particular SKU in a week is atleast half of the maximum number of stores selling a particular SKU. For example, say Week 10 was the week in which all 110 stores sold rugs while in the other weeks maybe 30, or 40 stores sold rugs, Week 10 is the maximum week. I only want to average the week where atleast 55 stores sold the rug. This is what I have so far but it does not work.

       

      AVG(aggr(

       

      if(

       

      count(DISTINCT {$<Year=,

      Period=,

      Week=,

      [Year Period]=,

      [Year Week]=,

      Date={">=$(=date(date(vCurrentWeekStart)-364))<=$(=date(date(vCurrentWeekStart)-1))"},

      SKU=P(SKU)>} [Store #])>0.5*MAX(TOTAL AGGR(COUNT(DISTINCT[Store #]),Week,SKU,[Sub Class])),

       

       

      sum({$<Year=,

      Period=,

      Week=,

      [Year Period]=,

      [Year Week]=,

      Date={">=$(=date(date(vCurrentWeekStart)-364))<=$(=date(date(vCurrentWeekStart)-1))"},

      SKU=P(SKU)>} [POS Net Units]))

       

      ,Week,SKU,[Sub Class]))

       

       

       

       

      Thanks!

        • Re: Aggregate Average
          Dariusz Mielczarek

          Isabel,

           

          try with Dimension Limits. In straigh table you may define criteria on 1-st expression results. So, do you first expression as count distinct number of stores for week and SKU. Then for dimension "week" define, Greather than 50% relative to total.

          It should work...

          If not, let me know, i will try to help to find another way.

           

          regards

          Darek