Set Analysis

[avastani]

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.

[markmccoid]

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

[avastani]

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.

[markmccoid]

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

[#5]

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:

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:

[avastani]

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

[#7]

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