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.




        • 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




            • Re: How to achieve this
              Jeff Menzie

              Here's a non-aggr solution:





                  Year (PeriodDate) as PeriodYear,

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




              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) / count (distinct PeriodMonth)


              For the standard deviation, easiest to use aggr:






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




              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.