Skip to Content
Author's profile photo Former Member

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)




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

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Good one 😳

      Author's profile photo William MARCY
      William MARCY

      Great Webi trick !

      You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !