Skip to Content

Business requirement: Display 3-month running average for sales revenue in a 12-month timing window for each year, like the one below

/wp-content/uploads/2014/02/1_444045.jpg

This chart helps the business understand how well they perform in term of sales revenue in each year after slightly adjusting data for seasonality.

Here’s how we can achieve that.

Step 1: Build a report using eFashion Universe

/wp-content/uploads/2014/02/1_400059.png

Step 2: Create “3-month Running Average” variable for sales revenue

=Previous(Previous(RunningSum([Sales revenue]);0)-Previous(RunningSum([Sales revenue]);3);1)/3

/wp-content/uploads/2014/02/3_444046.jpg

Step 3: Extract “3-month Running Average” for each year (2005 & 2006)

/wp-content/uploads/2014/02/2_400060.png

And

/wp-content/uploads/2014/02/3_400064.png

If you add these 3 variables in the original table, you can see that the running average of sales revenue is extracted from the first calculation for each year.

/wp-content/uploads/2014/02/2_444063.jpg

We still need one more step to display data correctly. We cannot use 2005 RA and 2006 RA because WebI may get confused between 2005 and 2006 data. You can add the [Year] dimension to the chart, but you may not have a clear look for trending performance in each year.

Step 4: Clean running average data for each year so that they can be displayed on the chart separately.

Create 2005 variable

=Sum([2005 RA] In([Year];[Month]))

Create 2006 variable

=Sum([2006 RA] In([Year];[Month]))

Now we should be able to assign data to the chart as requested

/wp-content/uploads/2014/02/5_400066.png

And then we have the final result:

/wp-content/uploads/2014/02/1_444045.jpg

Huu Nguyen

UPDATE: The formula for running average has been corrected in Step 2

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply