WebI Trick: Line chart to show running average for each year

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

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

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

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

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

And

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.

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

And then we have the final result:

Huu Nguyen

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