Query on Slope-Based Sales Trend Analysis in OAC

Jayakumar Perumal

Hi Team,

We have a requirement to identify customers whose sales have declined over a specific period in the last one year. Essentially, we need to compare the last 12 months of sales data and determine the trend — similar to how the SLOPE function works in Excel.

Could you please confirm if Oracle Analytics Cloud provides any built-in functionality for this type of analysis? Additionally, do we have any extensions or sample reports available for this scenario?

Thanks,
Jay

Brendan T

You can add overlay, projections, forecasting and reference lines to charts, and for other types of analytics https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/enhance-visualizations-statistical-analytics.html#GUID-530477EB-EF4F-40FA-9131-F0E50F0C2506

Jayakumar Perumal

Hi Brendan,

Thanks for the quick response. We have data for hundreds of customers to compare — will this chart help us identify the declining sales customers efficiently?

Thanks,
Jay

Gianni Ceresa

The SLOPE function in Excel doesn't do anything magically, it's a documented simple calculation on a dataset with 2 values (referred as X and Y):

Take each x value, subtract the average of all the x's values, multiply by each y value subtracting the average of all the y's values. Sum all that together and divide by the sum of each x value subtracted the average x squared. There is your slope value.

You can therefore calculate that in OAC because OAC is able to calculate the average in a dataset, it is able to perform subtraction, multiplication and division. And it can also perform sum aggregation across a dataset.

There isn't a native function doing that, but you don't need it because the function itself is just a combination of simpler operations.

If you have hundreds of customers and want this value to be calculated all the time and take actions based on it, you can also materialize it in your data source: it will be calculated once and stored, making it faster to perform analysis on it.

Gianni Ceresa

And going in the direction hinted by Brendan: the FORECAST function let you easily forecast future sales by customer based on past data ( https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/functions.html ).

That could give you an added value than just the slope of the regression line, because the forecast could take into consideration a not linear trend and seasonality etc.

Jayakumar Perumal

Thanks @Gianni Ceresa . i will try out your suggestions and update my results.

Joseph Mathew

The idea of using the Forecast functions sounds interesting. However, in my experience with OAC, we seem to be able add this forecast feature only on charts. Can this function be added on a pivot table or a simple table viz?

For eg: Lets say we have onhand data for 100 SKUs ( Inventory Items) over the last 12 months - 31-Oct-24 through 31-Oct-25. We can create a pivot table that shows this data where SKUs are in Rows and month end date as columns. The supplychain team wants to know which products are increasing in quantity over the month which ones are stable, while others are declining.

The ones that are growing are exceptions that need action.

Next, the user may want to compare data from 31-Oct-23 to 31-Oct-24 to see how these items behaved the previous year.

The user may need to change the date range instead of prior 12 months to prior 24 months.

The user may want to use a Product Category field ( a group of items) to perform the same analysis.

So just within Inventory function and a simple on hand quantity trend, the requirement can scale all over the time line map.

When we look at this way, the same type of analysis will become useful in Payables, Receivables, where moving from summary to detail becomes easy and users can track exceptions.

Thanks

Joseph

Gianni Ceresa

FORECAST is a function, you can add a calculation and use the function in there. Then just add that column in any visualization where you need it. You can also make it "smarter" by merging your real measure when it exists and the forecast for the period when you don't have real values by using a condition, and then just use that calculation instead of your measure.