GROUP_MOVING_AVERAGE function usage example.

Goal

Use an example to better understand the usage of the GROUP_MOVING_AVERAGE function.

Learn

The GROUP_MOVING_AVERAGE function returns an average line of values within a single group. To achieve expected results from this function, all values should belong to one group.

• If we have two columns on our Source Sheet - Date and ID. The goal is to find out how many unique IDs occur for certain date and then get a trend on number of unique IDs per day.
  
  
  
  
• On your data Preparation Sheet (Prep), create new columns with the formulas GROUPBY(#Source!Date) and GROUPCOUNTDISTINCT(#Source!ID) to get the number of unique IDs for a certain date (let's call it DistinctIDs).
  
  
  
  
• Create a new Sheet (Result) and in the first column add the formula GROUPBY(1) to create a single group, then GROUP_SORT_ASC(#Prep!Date) to get all dates sorted and finally add a reference to #Prep!DistinctIDs to bring DistinctID values to the Final Sheet.
• After these preparation steps, everything is ready to use the GROUP_MOVING_AVERAGE function - that is to say, all values are within a single group.
• Apply the formula GROUP_MOVING_AVERAGE(#Result!DistinctIDs;<K>) on your Final Sheet to get the desired result.