OACS : Moving Average

Bharath Raghupathi-Oracle

Summary:

Hello,

We are trying to calculate 5 day Moving average for Sales data. Sample data and expected result as below.

We are using MAVG function of OACS. However we cannot pass group by Product to this function which is skewing the results of Product P2 and P3.

Example : Without the group by clause, Product P2 is picking up rows from P1 in calculating the MAVG and this results in incorrect results.

Any suggestions will be of great help.

Content (required):

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

Joshua C. Stewart

The method below is only feasible with a small amount of Products.

You could create a separate analysis for each product grouping and in the measure parameter you can filter to specific products:

MAVG(FILTER(SALES USING PRODUCT = 'P1'), 5)

Otherwise, you can use the EVALUATE_AGGR function which allows Aggregate Functions with GROUP BY

Michal Zima

I guess, you can use in MAVG syntax (as for any aggregation function) BY <<column>> clause, where you specify resetting column for MAVG function - thus aggregation will be reset when value of <<column>> changes. So in your case it should look like: MAVG(SALES ,5 BY PRODUCT). "Disadvantage" is, that you need to specify custom calculation for each combination you will need.

ALOK SH-Oracle

Hi Bharath ,

Please put product column name in group by under Advanced Tab option and apply then it will work.

Please check once.

Bharath Raghupathi-Oracle

@ALOK SH-Oracle - Thanks for this solution, Yes it works fine in OACS classic. Is there anyway we can do this in DV workbook ?

Bharath Raghupathi-Oracle

Thanks @Joshua C. Stewart for your help. I ended up using EVALUATE_AGGR functions as we have over 100k products.

Michal Zima

@Bharath Raghupathi-Oracle Have you tried approach, i was reccomending to you ? Using custom calculation in DV: MAVG(SALES ,5 BY PRODUCT) ? This should definitelly work….