CODEWITHSUNDEEP
looker-studio
Maharshi Raval
Maharshi Raval

Reputation: 278

Running Average in Google Data Studio

Monthly Running Average with Daily SUMS on Graph

I have data as Follows (SAMPLE):

enter image description here

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.

Upvotes: 7

Views: 8995

Answers (1)

Taavi Randmaa
Taavi Randmaa

Reputation: 321

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

Upvotes: 2

Related Questions