Let's start by looking at our data.

Sales by Month |

SCRIPT_REAL("

library(forecast)

year <- .arg1

month <- .arg2

sales <- .arg3

len <- length(sales)

## Sorting the Data

date <- year + month / 12

dat <- cbind(year, month, sales)[sort(date, index.return = TRUE)$ix,]

## Decomposing the Time Series

timeser <- ts(dat[,3], start = c(dat[1,1], dat[1,2]), end = c(dat[len,1], dat[len,2]), frequency = 12)

as.vector(stl(timeser, s.window = 'period')[[1]][,2])

",

ATTR( YEAR( [Order Date] ) ), ATTR( MONTH( [Order Date] ) ), SUM( [Sales] ) )

Now, let's see what it looks like.

Sales by Month (with Tableau and R Trends) |

Sales by Month (with Tableau and R Trends) (without 2009) |

Sales by Quantity (with Trend) |

as.vector(stl(timeser, s.window = 'period')[[1]][,1])

Let's see what it looks like.

Sales by Month (with Seasonality) |

as.vector(stl(timeser, s.window = 'period')[[1]][,3])

Sales by Month (with Remainder) |

Finally, let's look at a way to predict new observations using seasonal decomposition. With seasonal decomposition, our predictions would always be equal to the trend plus the seasonal effect. For new observations, we will always have the seasonal effect because it repeats every year. However, we won't have the trend. There are plenty of different methods you could come up with to determine the trend. For this method, we'll just say that the trend for new observations is equal to the last trend we know (December 2012). So, once we add a few blank lines to our data set (which we've done before to create new observations), we can create our calculation. Here are the last few lines:

seasdec <- stl(timeser, s.window = 'period')

for(i in 1:len.new){if(dat.new[i,2] == month.orig[len.new + 1]){start <- i; break;}}

pred <- seasdec[[1]][start:(start + hold.orig[1] - 1),1] + seasdec[[1]][len.new,2]

c(rep(NA,len.new),pred)

Let's see what it looks like.

Sales by Month (With Forecast) |

Brad Llewellyn

Data Analytics Consultant

Mariner, LLC

llewellyn.wb@gmail.com

https://www.linkedin.com/in/bradllewellyn

Data Analytics Consultant

Mariner, LLC

llewellyn.wb@gmail.com

https://www.linkedin.com/in/bradllewellyn

**Appendix**

**Sales (Trend)**

**SCRIPT_REAL("**

library(forecast)

year <- .arg1

month <- .arg2

sales <- .arg3

len <- length(sales)

## Sorting the Data

date <- year + month / 12

dat <- cbind(year, month, sales)[sort(date, index.return = TRUE)$ix,]

## Decomposing the Time Series

timeser <- ts(dat[,3], start = c(dat[1,1], dat[1,2]), end = c(dat[len,1], dat[len,2]), frequency = 12)

as.vector(stl(timeser, s.window = 'period')[[1]][,2])

",

ATTR( YEAR( [Order Date] ) ), ATTR( MONTH( [Order Date] ) ), SUM( [Sales] ) )

**Sales (Seasonality)**

**SCRIPT_REAL("**

library(forecast)

year <- .arg1

month <- .arg2

sales <- .arg3

len <- length(sales)

## Sorting the Data

date <- year + month / 12

dat <- cbind(year, month, sales)[sort(date, index.return = TRUE)$ix,]

## Decomposing the Time Series

timeser <- ts(dat[,3], start = c(dat[1,1], dat[1,2]), end = c(dat[len,1], dat[len,2]), frequency = 12)

as.vector(stl(timeser, s.window = 'period')[[1]][,1])

",

ATTR( YEAR( [Order Date] ) ), ATTR( MONTH( [Order Date] ) ), SUM( [Sales] ) )

**Sales (Remainder)**

**SCRIPT_REAL("**

library(forecast)

year <- .arg1

month <- .arg2

sales <- .arg3

len <- length(sales)

## Sorting the Data

date <- year + month / 12

dat <- cbind(year, month, sales)[sort(date, index.return = TRUE)$ix,]

## Decomposing the Time Series

timeser <- ts(dat[,3], start = c(dat[1,1], dat[1,2]), end = c(dat[len,1], dat[len,2]), frequency = 12)

as.vector(stl(timeser, s.window = 'period')[[1]][,3])

",

ATTR( YEAR( [Order Date] ) ), ATTR( MONTH( [Order Date] ) ), SUM( [Sales] ) )

**Sales (Forecast)**

**SCRIPT_REAL("**

library(forecast)

## Creating vectors

hold.orig <- .arg4

len.orig <- length( hold.orig )

len.new <- len.orig - hold.orig[1]

year.orig <- .arg1

month.orig <- .arg2

sales.orig <- .arg3

## Sorting the Data

date.orig <- year.orig + month.orig / 12

dat.orig <- cbind(year.orig, month.orig, sales.orig)[sort(date.orig, index.return = TRUE)$ix,]

dat.new <- dat.orig[1:len.new,]

## Decomposing the Time Series

timeser <- ts(dat.new[,3], start = c(dat.new[1,1], dat.new[1,2]), end = c(dat.new[len.new,1], dat.new[len.new,2]), frequency = 12)

seasdec <- stl(timeser, s.window = 'period')

## Predicting the new Observations

for(i in 1:len.new){if(dat.new[i,2] == month.orig[len.new + 1]){start <- i; break;}}

pred <- seasdec[[1]][start:(start + hold.orig[1] - 1),1] + seasdec[[1]][len.new,2]

c(rep(NA,len.new),pred)

",

ATTR( YEAR( [Order Date] ) ), ATTR( MONTH( [Order Date] ) ), SUM( [Sales] ), [Months to Forecast] )

Hi, Mr. brad

ReplyDeleteThank to share this material,

I have tried your appendix in Tableau 9.0

But when I tried to visualize sales (forecast), I couldn't get same result as your blog.

How you to add a month dimension in the Tableau?

Thanks