Page Comparison

Syntax

GROUP_MOVING_AVERAGE(<number>;[<number>])

Values: time series values

K: number of past values to compute the moving average from

Description

This function smooths a time series by applying a simple moving average of the past k values. Smoothing a time series helps remove random noise and leave the user with a general trend.  You might use  GROUP_HOLT_WINTERS  for exponential smoothing .

This is an  aggregate function .

Example

In the following example you have average stock price values per month over a three year period. Try to smooth the data to find the general trend.

Image Removed

Next, duplicate this source into a calculation sheet in order to work on it.

Click the  Fx  button on the formula line to display the formula builder (As of Datameer 7.2, the formula builder is located in the worksheet inspector) and select GROUP_MOVING_AVERAGE

Use the Avg_Price as the Data and set the k at 5. This uses each previous 5 months to create a smoothed average for each data record.

Image Removed

The first record in the column MovingAverage_Price_5 is an average of the first 5 records of Avg_Price.

Image Removed

As the Moving_Average_Price_5 records are the average of the previous 5 Avg_Price records, the Moving_Average needs to match to the median of the Date and Ave_Price.

Create a new sheet and click the first open and open the Formula Builder. Select GROUP_PREVIOUS to shift the Date column down two rows.

Image Removed

Use the same GROUP_PREVIOUS function to shift the Avg_Price down by two rows.

Image Removed

Finally, COPY the MovingAverage_Price_5 into the sheet.

The moving average values are now properly aligned with the median date and price the moving average is based on.

Image Removed

After having completed the workbook, a line chart would be a good tool to visualize your new smoothed data compared to your actual data.

Image RemovedRegular expressions are a way for matching strings of text, e.g., particular characters, words, or phrases. They can be used in searching, editing and manipulating your data. You can use Java-style regular expressions in formulas by typing them into the formula or using them in a filter. Use them to process text for items such as email addresses, file names and extensions, or company names.

Using Regular Expressions in Formulas

1. Click a column in a workbook. Either the current formula is displayed above the workbook, or the formula builder appears.
2. Create or edit the formula using regular expressions. Then press Enter.

Example:

If your workbook had a column listing all the users of your website, you could use the MATCHES() function and .*\d+.* to find all user names containing numbers.
Or you could use the REGEXTRACT() function and [JMS].* to tokenize all user names that start with "J", "M" or "S".

Using Regular Expressions in Filters

Here is a quick overview of how filters work with regular expressions. Refer to Filtering Data to learn more about filters.

Using regular expressions in simple filters:

1. Click the Filter icon.
2. Select the data to be filtered. Only string data support regular expressions. Select matches regexp from the drop-down list.
3. Create the regular expression.
4. Click the + (plus) button to add more columns to the filter if desired.
5. Click Filter Sheet.

Using regular expressions in advanced filters:

1. Click the Filter icon.
2. Click the Advanced tab.
3. Enter a function which uses regular expressions as an argument.
4. Add more functions using the logical AND or OR operators (&& or || respectively)
5. Click Filter Sheet.

Useful Links

More information about regular expressions can be found on the web. Two sites we recommend are the Java Tutorials and 10 Regular Expressions You Should Know.
If you have created a regular expression and want to make sure that it is correct or if you need to change it into a java string then RegexPlanet's Regular Expression Test Page is just what you need.

See Importing with Regular Expressions to learn more.