cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member
Forecasting using a macro trend (moving average) and seasonality (seasonal index) is a common way to forecast data into the future. The two components, seasonal index and moving average, are based on prior historical trends. They come together to form a model that can be projected out for the near future.  

Seasonality (Seasonal Indexing)

Seasonal indexing is the process of calculating the high’s and low’s of each time period into an index. This is done by finding an average for an entire set of data that includes the same number of matching periods, then dividing the individual period average into that total average. This gives us an index whose total is the number of periods in a full cycle.
Example: There needs to be the same number of periods for each cycle. For a trailing 36 month period, there are 3 Januarys, 3 Februarys, etc. This is to not give a single month more/less weighting. Once we have the average for each month within that time period, we can divide that into the average for the entire time period. This giving us the general seasonality for each month regardless of year.
Average(Revenue for all Januarys)/Average(Revenue for all months) = January Index
 

Macro Trend (Moving Average)

The moving average calculates the macro trend that has been occurring for recent periods. In a monthly model, this would be the average for the prior full 12 months.
Example: If it is currently August 2015, the periods included would be August 2014 to July 2015. We do not include August 2015 because it has not concluded yet. We do not have all the necessary data.

Combining the Moving Average and Seasonal Index

To get a forecast for future dates, simply multiply the moving average and the corresponding seasonal index for the forecast month.  The results will be the forecast value for each month going forward.   

Creating this Model in SiSense

Seasonal Index SQLNote that this takes in daily data and builds a monthly model.  

SELECT month,product, (value/avg_value) AS index

from

 ( 

 SELECT a1.month, a1.product, sum(a1.value) AS value, a4.avg_value

 from


(SELECT getmonth(<DATE>) AS month, <PRODUCT>, <VALUE> AS value FROM <TRANSACTIONS>) a1


 cross join

(SELECT AVG(value) AS avg_value

 from

(SELECT month, product, sum(value) AS value

 from


(SELECT getmonth(<DATE>) AS month, <PRODUCT>, <VALUE>AS value FROM <TRANSACTIONS>) a2


GROUP BY month, product

)a3

 )a4

GROUP BY month, product, avg_value

 )a5

The resulting dataset should look like this:

Community_Admin_0-1634474863372.png

 

Moving Average SQL

This SQL finds the macro trend for recent performance.  The table’s output contains a forecast first of month, month, the dimension (product), and the forecast value (trailing 12 month average).  Simply replace the same fields/table as above.

SELECT 

addmonths(FirstOfMonth,12) AS forecast_FirstOfMonth,

b2.month,

b2.product, 

avg(a3.value) AS value

from

 

 (SELECT year, month,createdate(year,month,1) AS FirstOfMonth, product, sum(value) AS value

 from

 ( 

 SELECT getyear(<DATE>) AS year, getmonth(<DATE>) AS month,<PRODUCT>,<VALUE>

 FROM <TRANSACTIONS> t

 WHERE <DATE> BETWEEN createdate(getyear(addmonths(now(),-12))

 ,getmonth(addmonths(now(),-12))

 ,1)

 AND 

 adddays(createdate(getyear(now()),getmonth(now()),1),-1)

 )a1

 

 GROUP BY year, month, product

 )b2

FULL OUTER join

 

 (SELECT product,avg(value) AS value

 from

 

 (SELECT year, month,createdate(year,month,1) AS FirstOfMonth, product, sum(value) AS value

 from

 ( 

 SELECT getyear(<DATE>) AS year, getmonth(<DATE>) AS month,<PRODUCT>,<VALUE>

 FROM <TRANSACTIONS> t

 WHERE <DATE> BETWEEN createdate(getyear(addmonths(now(),-12))

 ,getmonth(addmonths(now(),-12))

 ,1)

 AND 

 adddays(createdate(getyear(now()),getmonth(now()),1),-1)

 )a1

 

 GROUP BY year, month, product

 )a2

 GROUP BY product

 )a3

ON a3.product = b2.product

GROUP BY 

FirstOfMonth,

b2.month, 

b2.product

ORDER BY 

firstofMonth

The resulting dataset should look like this.

Community_Admin_1-1634474915480.png

The resulting tables should be connected as seen below.

Community_Admin_2-1634474915351.png

In the dashboard, the formula is as seen below.  Sum(CMA_Rolling12.Value * Index.Index)

Community_Admin_3-1634474915379.png

Final Result

Community_Admin_4-1634474915400.png

Happy Forecasting!

Version history
Last update:
‎10-17-2021 05:49 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request