In this Tips and Techniques article, let’s discover one of our date and time functions, the DATERANGE function.
The DATERAMGE function is a little different from the other date and time functions in that it is not necessary to reference a column or row of dates in a data source file. The DATERANGE function can create its own series of dates based on the formula you build.
Before you begin, you should be familiar with:
- Our article on the DATE and DATEVALUE functions.
- How to express the date format you want to display. For example, dd/MM/yy-ww (day/month/year-week).
In this formula, the DATE_STARTOF function specifies a start date of one week ago and the DATE_ENDOF function specifies an end date of today, returning week 20 of 2014.
Tip: To check this, wrap the DATE_STARTOF function or the DATE_ENDOF function in the DATEVALUE function. Use the Redo change to formula button to remove the DATEVALUE function.
Tip: You can easily reference the DATERANGE formula in a LOOKUP formula by using the Insert Reference to another component (REF) button on the formula bar.
Let’s now look at our Support example.
In this example, the DATERANGE function on the X:Axis is not using our data source file. The Series refers to the X: Axis formula, and also to column C in our data source file.
The DATE_CONVERT function ensures that the date format for column B is in the same format as the date format of the X: Axis.
The formula for X:Axis called
(week20 – 11 (Sun)-17 (Sat) May 2014) is:
DATEVALUE( DATERANGE( DATE_STARTOF( TODAY( ) , week , relative value , first ) , DATE_ENDOF( TODAY( ), week , relative value , first ) , “d/MM/yyyy-ww” ) , “dd-MM-yy-ww” , timezone )
Result: Returns days for week 20 of 2014.
The formula for the Series is:
LOOKUP( week20 – 11 (Sun)-17(Sat) May 2014 , DATE_CONVERT( B:B , “MM/d/yy” , “dd-MM-yy-ww”) , C:C )
Result: Returns values for week 20 of 2014
This Klip uses the following data source file. When building this Klip ensure you have data that contains dates for the last week of the current year.
Now that you know more about the DATE and DATEVALUE functions, 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: