6 Replies Latest reply: Sep 19, 2016 8:21 PM by Sunny Talwar RSS

    Like For Like Set Analysis

    Matt Pierce

      Hi All,

       

      I trying to do some like for like analysis looking at the previous month and last year and I'm stumped on the syntax.  The data looks something like this:

       

      MonthYearLastMonthLastYearsum(Head_Flag)
      Sep-2015Aug-2015Sep-20142192
      Oct-2015Sep-2015Oct-20142211
      Nov-2015Oct-2015Nov-20142207
      Dec-2015Nov-2015Dec-20142196
      Jan-2016Dec-2015Jan-20152219
      Feb-2016Jan-2016Feb-20152224
      Mar-2016Feb-2016Mar-20152185
      Apr-2016Mar-2016Apr-20152172
      May-2016Apr-2016May-20152173
      Jun-2016May-2016Jun-20152146
      Jul-2016Jun-2016Jul-20152129
      Aug-2016Jul-2016Aug-20152109
      Sep-2016Aug-2016Sep-20152093

       

      I'm trying to add three additional columns, Headcount last month, Head count last year and Average headcount between last year and current month.

       

      The current month is provided as a selection criteria ultimately resulting in the straight table containing one row of data.

       

      To get last month and Last Years headcount this syntax seems to work as long as none of the dimensions in the straight table are dates:

       

      Last Month:Sum({<MonthYear = P(Previous_MonthYear)>}Head_Flag)

      Last Year:Sum({<MonthYear = P(LastYear)>}Head_Flag)

       

      I'm struggling to find the right syntax to do a between statement in the set analysis that refers to LastYear and MonthYear  for the final column.

       

      Any help is much appreciated

       

       

        • Re: Like For Like Set Analysis
          Sunny Talwar

          May be try this:

           

          Last Month

          Above(Sum(Head_Flag))

           

          Last Year

          Above(Sum(Head_Flag), 12)

            • Re: Like For Like Set Analysis
              Matt Pierce

              Hi Sunny, thanks for the response.  I've had a play with the above function and looked at the documentation.   It certainly works until I make a selection criteria of a specific month at which point the function returns no results.

               

              Before Selection criteria:

               

              MonthYearSum(Head_Flag)above(Sum(Head_Flag))Above(Sum(Head_Flag), 12)
              Sep-20152192--
              Oct-201522112192-
              Nov-201522072211-
              Dec-201521962207-
              Jan-201622192196-
              Feb-201622242219-
              Mar-201621852224-
              Apr-201621722185-
              May-201621732172-
              Jun-201621462173-
              Jul-201621292146-
              Aug-201621092129-
              Sep-2016209321092192
              Oct-2016209120932211

               

               

              Once selection criteria is set:

               

              MonthYearSum(Head_Flag)above(Sum(Head_Flag))Above(Sum(Head_Flag), 12)
              Sep-20162093--

               

              I was able to get a cumulative total using RangeAvg(Above(Sum(Head_Flag),0,12)) but again this only works if no selection criteria are made.

               

              Thanks again for the assistance

                • Re: Like For Like Set Analysis
                  Sunny Talwar

                  Try this:

                   

                  1 month above

                  Above(Sum({<MonthYear, Month, Date, Year>}Head_Flag)) * Avg(1)

                   

                  12 months above

                  Above(Sum({<MonthYear, Month, Date, Year>}Head_Flag), 12) * Avg(1)


                  12 month rolling average

                  RangeAvg(Above(Sum({<MonthYear, Month, Date, Year>}Head_Flag), 0, 12)) * Avg(1)

                    • Re: Like For Like Set Analysis
                      Matt Pierce

                      Hi Sunny,

                       

                      Thanks for all the assistance.  I continued reading up on the Above function after your last suggestion and ended up with the following:

                       

                      Only({<MonthYear=>}Aggr(RangeAvg(Above(Sum({$<MonthYear=>}Head_Flag),0,12)), MonthYear))

                       

                      Looks pretty similar to your suggestion above.  Can I ask what is the *Avg(1) doing in your example above?

                       

                      I'm also trying to limit the results to the current month (without the user specifying it via a selection criteria.  I've settled on this code, but it has some issues if the user does actually use a selection criteria you get all the months up to the maximum from the set analysis:

                       

                      Only({<MonthYear={">=$(=max(MonthYear))"}>}Aggr(RangeAvg(Above(Sum({$<MonthYear=>}Head_Flag),0,12)), MonthYear))

                       

                      Thanks Again!

                        • Re: Like For Like Set Analysis
                          Sunny Talwar

                          May be try this:

                           

                          Only({<MonthYear = {">=$(=Min(MonthYear))<=$(=Max(MonthYear))"}>} Aggr(RangeAvg(Above(Sum({$<MonthYear>} Head_Flag),0,12)), MonthYear))

                           

                          or this:

                           

                          Only({<MonthYear = {"$(=Max(MonthYear))"}>} Aggr(RangeAvg(Above(Sum({$<MonthYear>} Head_Flag),0,12)), MonthYear))

                      • Re: Like For Like Set Analysis
                        Sunny Talwar

                        Attaching a sample for you to have a look at. I am only ignoring selection in MonthYear field because I don't have other date and time fields, but you need to ignore all date and time related fields where you can possibly make selections and you don't want them to impact the result of your calculation.

                         

                        Capture.PNG