5

Date Range Dropdown / Period Selector

In this article, I will go over how to build a date dropdown menu that filters your data based on specific date ranges (ie last 7 days, MTD, etc):

 

The data we will be using for this example looks like:

 

The main function we will need to use to build this is a Switch statement, which will allow us to take into account each case

 

The first thing we need to do is build our User Input Control with a Control Type Drop-Down List

 

Then, we will need to set a variable for this User Input Control.  I called mine "dates"

 

The next step we will have to do is to set our values for the user input control in the form of an Array

 

If I want to filter for last 7 days, last 30 days, month to date and last month, my values will look something like this:

 

And my labels will look like this:

 

Now that we have built our User Input Control, we can begin to build our Switch statement which goes into the series of the bar/line chart or the current value of the gauge.


 
The Switch statement has 3 parameters; the data, the case and the value:

 

For this statement, the data will be the variable we set with the User Input Control



The cases will be the data within the array for the values of the User Input Control

The value is our Between statement that filters the data based on date

 

6 comments

  • 0
    Avatar
    Rahul Bura

    This is handy! Thanks for the post for period selectors, date range selectors etc.!

  • 0
    Avatar
    Mason Taylor

    Thanks Garrod , wonderful explanation! :)

    Really helped !

  • 0
    Avatar
    Ivan Stefanovski

    Awesome article and great explanation Garrod, thanks! 

  • 0
    Avatar
    Estiven Garcia

    Great Garrod!!

  • 0
    Avatar
    Utku Demirhan

    Hello how to run this with google analytics data. I wrote this but it didnt work.

     

    SWITCH($dates,
    "-7",SELECT(slice(@B:B;), BETWEEN(DATE(slice(@B:B;),"yyyyMMdd"),DATE_ADD(TODAY(),"5", -7), TODAY())),
    "-30", SELECT(slice(@B:B;), BETWEEN(DATE(slice(@B:B;),"yyyyMMdd"),DATE_ADD(TODAY(),"5", -30), TODAY())),
    "MTD",SELECT(slice(@B:B;), BETWEEN(DATE(slice(@B:B;),"yyyyMMdd"),DATE_STARTOF(TODAY(),"3"), TODAY())),
    "Last Month",SELECT(slice(@B:B;), BETWEEN(DATE(slice(@B:B;),"yyyyMMdd"),DATE_STARTOF(TODAY(),"3",-1), DATE_ENDOF(TODAY(),"3",-1)
    )))

     

    Data looks like this:

     

    ga:landingpagepath   ga:date      ga:new user .....

          firstlanding         20170801 .        1

          secondlanding . 20170801 .        1

     

    Thank you for any advice

  • 0
    Avatar
    The Data Girl

    Hi Utku,

     

    To me formula looks correct, what are you running into? have you seen an easier version of the period filtering using the New Date Filtering - Date Drop Down / Period Selector ?

     

     

Please sign in to leave a comment.