Running Average in Google Data Studio

user_1769135 0
Viewed 5743

Monthly Running Average with Daily SUMS on Graph

I have data as Follows (SAMPLE):

enter image description here

  • There would be multiple Orders Per Day.
  • Need to Track Number of Orders per Day. In a Graph. (This is Straight for forward with Order Column Defaulting to 1 and then summing it.)

The thing that I would like to achieve is additional metric of 30-Day Moving average of along with the Daily Total of WorkOrders

Sample of what I have already achieved in Data Studio:

enter image description here

Along with it, I want Pseudocode 

SUM ( Order 
WHEN 
DATE =( DATEBETWEEN ( CurrentRowDATE , CurrentRowDate - 30) ) 
) / 30

This would be the Average of Orders Per Day for the Past 30 Days.

Would Really Appreciate any Pointers. Appreciate your help in Advance.

google-data-studio
1 Answers

You can do this by blending your data source with itself using a cross join. I used this sample data on Google Sheets: sample data

Created the join: cross join

Then created the table: table definition

The formula for creating the running average:

SUM(
  IF(
    date (Table 2) BETWEEN DATETIME_SUB(date (Table 1), INTERVAL 2 DAY) AND date (Table 1),
    orders (Table 2),
    0
  )
) / 3
user_8560190
Related