5 Replies Latest reply: Nov 9, 2015 2:23 PM by Gopal Anandan RSS

    How to achieve this

      My data is has following cols

       

      Country, State, Period(in mm/dd/yyyy format), Qty

      A, A1, 1/1/2015, 10

      A, A2, 1/1/2015, 20

      A, A1, 2/1/2015, 25

      A, A1, 1/1/2014, 15

      A, A1, 2/1/2014, 10

      B, B1, 3/1/2015, 30

       

      I want to achieve the following output

       

      Year(Period)     Country     Sum(Qty) (for the Year)     Average Qty of each but the based on MONTHLY aggregation

      2015                   A           10+20+25=55                    Average of (10+20) and 25 = 55/2

      2015                   B            30                                   30/1 = 30

      2014                   A            15+10=25                         Average of 15 and 10 = 25/2

       

      I assume Aggr will have to be put to use - tried few options but gettig "-" as the answer.

       

      In addition to Average, I also want to get the Standard Deviation.

       

      Any help much appreciated.

       

      Thanks,

      Gopal

        • Re: How to achieve this
          Onno van Knotsenburg

          I suggest adding a Year and a Month field to your data model based on that Period field.

           

          Assuming you did that, you are going to make a Table with 2 dimensions:

          - Year

          - Country

          You are going to create two expressions:

          - sum(Qty)

          - avg(aggr(sum(Qty), Year, Month))

           

          Good luck!

          • Re: How to achieve this
            Massimo Grossi

            if your Period is always (at it seems from your table) on 1st of month

             

            sum(Qty)

            avg(aggr(sum(Qty),Period,Country))

            • Re: How to achieve this

              Here's a non-aggr solution:

               

              ExampleData:

              Load

                  *,

                  Year (PeriodDate) as PeriodYear,

                  Year (PeriodDate) & '-' & Month(PeriodDate) as PeriodMonth

               

              Inline

              [

              Country, State, PeriodDate, Qty

              A, A1, 1/10/2015, 10

              A, A2, 1/10/2015, 20

              A, A1, 2/10/2015, 25

              A, A1, 1/10/2014, 15

              A, A1, 2/10/2014, 10

              B, B1, 3/10/2015, 30

              ];

               

              In a straight table with country and state as dimensions, add two expressions:

               

              sum(Qty)

              sum (Qty) / count (distinct PeriodMonth)

               

              For the standard deviation, easiest to use aggr:

               

              sum(Aggr(stdev(Qty),PeriodYear,Country))

               

              20151108_2.GIF

               

              With Aggr, always make sure you have an aggregate function outside of the aggr. So you need:

               

              AggregationFunction1(Aggr(AggregationFunction2(field)))

               

              Without the outside aggregation function you may receive a hyphen. Here a hyphen frequently represents more than one value. The outside aggregation makes sure you're returning only one value.