You have already learnt about the DATERANGE function in an earlier Tips and Techniques article.
As you know, the DATERANGE function returns days between a start and end date. But in this article the X:Axis returns the month and the year for the last three months using the DATERANGE function.
The Series then references this formula and uses the GROUP and GROUPBY functions to return a unique instance of the month and year, and then sums related values. To average values, in the GROUPBY function select method and type in average(values) using the Insert literal string or number button.
Before you begin, you should be familiar with earlier articles on the following functions:
Tip: Get in the habit of evaluating your formulas to ensure the correct results are returned (with the Evaluate button ). Use the evaluate button if you suspect the data source uses count(values) as a method in the GROUPBY function to count the number of records being summed.
Now, let’s look at our Support Desk example:
The formula for X:Axis called DateRange is:
GROUP( DATEVALUE( DATERANGE( DATE_ADD( TODAY( ) , month , -2 ) , TODAY( ) , “yyyyMM” ) , “yyyy-MM” ) )
Result: Returns the last three months of this year:
2014-06, 2014-07, 2014-08
Note: Use the Insert literal string or number button to type in -2 days:
The formula for the Series is:
LOOKUP( DateRange, GROUP( DATE_CONVERT( A:A , “M/d/yy” , “yyyy-MM”) ) , GROUPBY( DATE_CONVERT( A:A , “M/d/yy” , “yyyy-MM” ) , B:B , method ) )
Result: Returns and sums the values for the last three months of the year: such as for August 710
The DATE_CONVERT function converts the date format that displays in the data source to the date format that displays on the X:Axis – year-month (yyyy-MM).
Skill Building Question: Why did I use the LOOKUP function? If you have June and July values, you can use the formula: GROUPBY( DATE_CONVERT( A:A , “M/d/yy” , “yyyy-MM” ) , B:B , method ) for the Series.
This Klip uses the following data source file:
Now that you know more about DATERANGE by month, check out the following tutorials:
- Tips and Techniques - Function examples
- Tips and Techniques - Task examples
- Tips and Techniques - Google Analytics examples
These articles provide an overview of important concepts for building Klips: