6 Replies Latest reply: Nov 27, 2013 4:24 AM by Lucian Moses RSS

    Set Analysis

    Amir Vastani


      How do I get a twelve-month rolling average over a given field using set analysis. I tried Only(Year) -1 and Only(Month) - 12 and Only(Date) - 365 but that doesn't seem to work. I am thinking I need Aggr as well?

      Any help is appreciated. Thanks in advance.

        • Set Analysis
          Mark McCoid

          Not sure how you had wanted to implement this, but if you were thinking of creating twelve expressions starting with today's month -12 up to the current month to get the rolling monthly numbers, you could use set analysis as follows:


          SUM({$<MonthOfYearNumber={$(=Num(Month(AddMonths(Today(),-12))))},CalendarYear={$(=Year(AddMonths(Today(),-12)))}>} FieldToAggregate)

          You would then change the -12 parameter on the AddMonths function to -11, -10, etc.

          Hope it helps.


            • Set Analysis
              Amir Vastani

              I was thinking of implementing it in one expression as opposed to 12 expressions. I think there is a way, just haven't come around to narrowing the method yet.


                • Set Analysis
                  Mark McCoid

                  So you want to have one total amount for the last 12 months?

                  In one statement I think the following would work.

                  SUM({$<Insert_MonthOfYearNumber={">=$(=Num(Month(AddMonths(Today(),-12))))"},Insert_CalendarYear={$(=Year(AddMonths(Today(),-12)))}>} $(NetRevenue))
                  SUM({$<Insert_MonthOfYearNumber={"<=$(=Num(Month(Today())))"},Insert_CalendarYear={$(=Year(Today()))}>} $(NetRevenue))

                  Not sure how to use the union operator in Set Analysis to combine this into one statement.

              • Set Analysis
                Dan English

                Here is an example of a moving avgerage expression that has the added twist of being dynamic (the slider determines the number of days in the moving average). This is done without Agrr() or set analysis (it dates back pre set anaysis/agg()) instead it uses RangeAverage() and Above().

                Sometimes Agg() and set analysis are so powerful they can blind us to other solutions. Just something to think about.





                if(TRADE_DATE > min(total TRADE_DATE) + vMovingAvg, rangeavg(above(avg(CLOSE_PRICE),0,vMovingAvg)), null())

                Calculated Dimension:

                =if(TRADE_DATE > min(total TRADE_DATE) + vMovingAvg, TRADE_DATE, null())

                Data Structure:


                  • Re: Set Analysis

                    Hi Dan,


                    I realise that this was an old post, but I hope you can help me as this seems to be the only solution that doesn't require a slider. For some reason the graphics/table you posted are not being displayed.  I tried to apply both formulas, but I get "No data to display" message on the graph. Also, I have two moving averages on one graph and your solution seems to provide for one only via a variable.