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

# Set Analysis

Hi,

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

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.

Mark

• ###### Set Analysis

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

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

In one statement I think the following would work.

`<blockquote><pre>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.

Mark
• ###### Set Analysis

I think so too. I am going to try now. Thanks Mark

• ###### Set Analysis

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.

Regards,

Dan

Chart:

Expression:

Calculated Dimension:

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.

Regards,

Lucian