Ok, I have MEASURE_1 with MAX aggregation and when I'm using PERIODROLLING like: PERDIOROLLING(MEASURE_1, -2,0) in results i see MAX VALUE from 3last months.

I must set max in rpd but on analysis I want to sum last 3months

Robert Angel

Sorry, maybe being slow, suffering from man flu here - are you asking for sum of last 3 max values?

Or do you just want sum of last three months?

choracy69

Yes, I am asking for sum of last 3 MAX values.

My mistake.

Robert Angel

Would this work for you - the principal of it and the underlying database; -

https://sonra.io/2011/06/01/time-series-in-obiee-analytic-functions-and-range-window-as-an-alternative-to-obiee-ago-func…

Gianni Ceresa

Because of MAX you need to do the sum yourself, so all you need is to use AGO twice to get the M-2 and M-1 value and add the current one.

So your formula will be <measure> + AGO( measure M-1 ) + AGO( measure M-2 )

this isn't a valid syntax, it just gives you the idea on how to do

Thomas Dodds

you want 3-month running sum of max values by month ...

you've got the max already so do an MSUM(max,3) and make sure your months are sorted correct ...

Example

This example shows a query that uses the MSUM function, along with example query results.

select month, revenue, MSUM(revenue, 3) as 3_MO_SUM from sales_subject_area

Result:

MONTH REVENUE 3_MO_SUM

JAN 100.00 100.00

FEB 200.00 300.00

MAR 100.00 400.00

APRIL 100.00 400.00

MAY 300.00 500.00

JUNE 400.00 800.00

JULY 500.00 1200.00

AUG 500.00 1400.00

SEPT 500.00 1500.00

OCT 300.00 1300.00

NOV 200.00 1000.00

DEC 100.00 600.00

NOTE: M01/JAN or ANY first value MSUM(Measure,#) = Measure ... keep that in mind if you are looking to bring forward an MSUM for your MO1/JAN