2 Replies Latest reply: Apr 5, 2012 7:44 AM by billknilton RSS

    max sales of last 6 months

      Hello,

       

      Can you help me with my issue? I have a database like this:

      skudateqntysales
      a01.01.20121030
      b02.01.20122060
      c02.01.2012515
      a05.01.20123090
      a10.01.20121030
      b03.02.20123060
      c03.02.201250150
      a25.02.20122060
      b07.03.20121030
      b10.03.201245135
      a10.03.20121560
      c15.03.20121030

       

      I want to get straight table, which will show me month in which was maximum sales during last 6 months and I want to see sum of this month too. This table must include all maximums for all sku. For example: if I choose  march 2012, it will show max sales since october 2011 and if I choose december 2011, it will show since july 2011.

       

      I try to use something like this

      max(aggr(sum({$<[Month Year]={">=$(=AddMonths(Max([Month Year]),-5))<=$(=AddMonths(Max([Month Year]),-0))"},month=>} [sales]),year,month,[sku]))

      but it worked only if i didn't choose any month.

      Do you have any ideas?

       

      Thanks for your help, Bill.

       

       

      some information:

      month, year - from another table like this:

      Datemonthquarteryear
      01.01.2012januaryQ12012
      02.01.2012januaryQ22012
      03.01.2012januaryQ32012
      04.01.2012januaryQ42012

       

      Month Year from script:

      MonthName(DATE(Date, 'DD/MM/YYYY')) AS [Month Year]

        • max sales of last 6 months
          Fernando Suzuki

          Try inserting the same set analysis expression in the outer max() function:

           

          max({$<[Month Year]={">=$(=AddMonths(Max([Month Year]),-5))<=$(=AddMonths(Max([Month Year]),-0))"},month=>} aggr(sum({$<[Month Year]={">=$(=AddMonths(Max([Month Year]),-5))<=$(=AddMonths(Max([Month Year]),-0))"},month=>} [sales]),year,month,[sku]))

           

          The concept is that the max() function also has to have the set analysis expression to override the current $ when you select any month.

           

           

          Hope this helps you.

          Regards,

          Fernando