3

Creating Start and End Date variables, from list of date ranges

Hi Klipfolio!

The following will detail how you can filter a component, by selecting a date range from a list(eg Last Month), assigning dates to start and end date variables based on that selection, and then using those start/end date variables to filter a component.

In our example, we are going to be filtering a table(although this can be any component), that looks like this;

What we want to do, is filter this table to only show dates, in a certain range that we pick.
For this example, I am going to have a large amount of possible date ranges, although you can pick and choose the ones you want.

1: Adding the date range control

First, add a user input control in the Klip.
In the Properties of the user Input control, assign it to a variable, in our case I created a new one called datePeriod.

Here is the formula I put in the Values of the user input control, which is every likely date range you would need.

ARRAY("Last 365 days","Last Year","Current Year to Date","Last 90 Days","Last 3 Months","Last Quarter","Current Quarter to Date","Last 30 Days","This Month","Last Month","Last 7 days","Last Week","Current Week to Date","Yesterday","Today")



2: Adding the Start and End date controls

Now, what we are going to do is add two more user input controls, one for the start date, and one for the end date.
However, we are going to make these hidden, unless we want the user to be able to see them.
 -
 




Next up is the tricky part, where we enter the formula for each formula for the date periods, using the SWITCH function.
Here is what you would have for the start date variable formula.
This would go inside of the Values of the hidden input control for the start date.
It can be copied and pasted directly, and you can feel free to remove any ranges you don't want.

SWITCH(
$datePeriod,

"Last 365 days",
DATE_ADD(TODAY(),"5",-365),//365 days ago

"Last Year",
DATE_STARTOF(TODAY(),"1",-1),//Start of last year

"Current Year to Date",
DATE_STARTOF(TODAY(),"1"),//Start of this year

"Last 90 Days",
DATE_ADD(TODAY(),"5",-90),//90 days ago

"Last 3 Months",
DATE_STARTOF(TODAY(),"3",-3),//Start of 3 months ago

"Last Quarter",
DATE_STARTOF(TODAY(),"2",-1),//Start of last quarter

"Current Quarter to Date",
DATE_STARTOF(TODAY(),"2"),//Start of this quarter

"Last 30 Days",
DATE_ADD(TODAY(),"5",-30),//30 days ago

"This Month",
DATE_STARTOF(TODAY(),"3"),//Start of this month

"Last Month",
DATE_STARTOF(TODAY(),"3",-1),//Start of last month

"Last 7 days",
DATE_ADD(TODAY(),"5",-7),//365 days ago

"Last Week",
DATE_STARTOF(TODAY(),"4",-1),//Start of last week

"Current Week to Date",
DATE_STARTOF(TODAY(),"4"),//Start of current week

"Yesterday",
YESTERDAY(),//Start of yesterday

"Today",
TODAY()//Start of today
)




This would be the similar formula for the End date Input Control Value.

SWITCH(
    $datePeriod,
    
    "Last 365 days",
    TODAY(),//Today
    
    "Last Year",
    DATE_ENDOF(TODAY(),"1",-1),//End of last year
    
    "Current Year to Date",
    TODAY(),//Today
    
    "Last 90 Days",
    TODAY(),//Today
    
    "Last 3 Months",
    DATE_ENDOF(TODAY(),"3",-1),//End of last month
    
    "Last Quarter",
    DATE_ENDOF(TODAY(),"2",-1),//End of last quarter
    
    "Current Quarter to Date",
    TODAY(),//Today
    
    "Last 30 Days",
    TODAY(),//Today

"This Month",
TODAY(),//Start of this month

    "Last Month",
    DATE_ENDOF(TODAY(),"3",-1),//End of last month

    "Last 7 days",
    TODAY(),//Today

    "Last Week",
    DATE_ENDOF(TODAY(),"4",-1),//End of last week

    "Current Week to Date",
    TODAY(),//Today

    "Yesterday",
    YESTERDAY(),//Start of yesterday

    "Today",
    TODAY()//Start of today
)


3: Filtering the table

Now, you have two variables, $startDate and $endDate, that give the unix start and end time of the date range you select from the list.


The next step is to actually use these to filter our table.
Previously, we needed to use a hidden data to filter the component.
However, if the component itself contains a date column, or an X-Axis formatted as a Date/Time in its Properties panel, we can filter on that directly, without needing to use a hidden data.

_____________________
_____________________
However, if there is no date column, or dates on the X-axis, you need to do the below:
What we are going to do is use hidden data to filter the table, such as in this example here.

First, right-click on the table, and add the hidden data.


We can rename the hidden data, to be called Date Filter.

Then, in the formula for the hidden data, we use the DATE function to convert the dates in our datasource to unix time, so we can compare them to our start/end date variables.


Make sure the format of the Data is set to be Number;




The last step is simply to apply a filter to the data;



And that's all!

I hope you all found this helpful.

Update: Adding a custom date range
There are some times when you want to give the user the option of picking from either a preset range, or their own start/end dates.
In this case, here is how this would be done.

First, we need to add a new option to the list of ranges, called 'Custom'.
Eg;

ARRAY("Last 365 days","Last Year","Current Year to Date","Last 90 Days","Last 3 Months","Last Quarter","Current Quarter to Date","Last 30 Days","Last Month","Last 7 days","Last Week","Current Week to Date","Yesterday","Today","Custom")


Then, we need to create two new date pickers, and add them to the Klip.
These will be using new variables, eg customStart and customEnd.
Make sure in the Properties panel, they are set to output in unix(seconds).


What you need to do after, is add this case to the SWITCH functions for the startDate and endDate variables.
Eg for startDate.

"Custom",$customStart


and for endDate

"Custom",$customEnd



Then, anytime you have chosen the 'Custom' option, the dates used will be those from the new date pickers.


Update 2: Adding a comparison period

This setup will allow you to get the start and end dates for a comparison period, based on a type of comparison period you pick.
When you do this, you cannot do the same filtering as mentioned above, as the applied actions filtering works on all parts of the component.

First, we need to add the input control for the comparison period.


The two options will be for the previous year, and previous period(Values of input control).

ARRAY("Prev period,Last year")


Then, we again need to add two hidden input controls, one for the $prevStart and one for the $prevEnd. 

For the previous start date;

IF(
$prevPeriod="Last year",
DATE_ADD($startDate,"1",-1),
DATE_ADD($startDate,"5",0-COUNT_DAYS($startDate,$endDate))
)


and for the previous end date;

IF(
$prevPeriod="Last year",
DATE_ADD($endDate,"1",-1),
DATE_ADD($endDate,"5",0-COUNT_DAYS($startDate,$endDate))
)


Warm regards,
Joshua
___________
Joshua Cohen-Collier
Technical Support Specialist, Klipfolio

14 comments

  • 0
    Avatar
    Arnie Chaudhuri

    Hi Joshua,

    Tank you so much for the article, It as very helpful to me while creating date range. However I would like to know how to add "Custom" date ranges in the datePeriod Input control?

    For example, Add "Custom" to datePeriod, upon clicking on "Custom " value, need to show start and end dates then give option to select dates by user 

     

    Is there any possibilities to achieve this?

     

    Thanks in Advance

  • 0
    Avatar
    Josh Cohen-Collier

    Hi Anusha,

    Glad you found the post helpful!

    To address this, I have added an update to the above post, with some more details.
    I hope this works for you!

    Warm regards,
    Joshua
    ______________________________
    Joshua Cohen-Collier
    Technical Support Specialist, Klipfolio

  • 0
    Avatar
    Jacob Norin Faarborg

    Hi Joshua

    Thank you for a very helpful guide. 

    I have just added a comparison period as you described and I see my two period dropdowns - but do I need to relate prevStart and prevEnd as a filter to ga:date like I did with startDate and endDate for it to actually compare the two periods? And how do I do that?

    And is it possible to add this month to the formular?

    Kind regards

    Jacob Norin Faarborg

  • 0
    Avatar
    Josh Cohen-Collier

    Hi Jacob,

    Filtering, in this case, would be something that is hard to give a boilerplate answer for, since it would vary quite widely depending on how your data is structured, and what you are looking to get out of it. For this same reason, while I can say you would need to relate the ga:date to the comparison period, it would not necessarily be a straightforward process depending on what you are looking to do.

    If you need more specific help, I would be happy to create a ticket for you so we can follow up via email?

    For the 'This month' comparison option, good catch! 
    I am adding this now.

    Warm regards,
    Joshua

  • 0
    Avatar
    Herman Broekhuizen

    Hello Joshua,

    Great post it's exactly what I was looking for!

    Is there also a possibility to hide the custom start and enddate date pickers if "Custom" is not selected in the preset dates?

    It's a little confusing that these date pickers will remain with 'old' dates if we select a new preset date.

    Regards,
    Herman

  • 0
    Avatar
    Josh Cohen-Collier

    Hi Herman,

    Thanks for the feedback, really glad you appreciate it!

    Unfortunately, this isn't something we can do.
    Sorry for the inconvenience here.

    Warm regards,
    Joshua

  • 0
    Avatar
    Carlos Lopez

    Hello Josh,

     

    Thank you very much for this amazing post! It's been of great help!

    I've followed all the steps and everything works fine, but I'm trying to use a "comparison period" in a Line Chart and I guess I'm missing one step because the chart does not show the new line associated with the comparison period. 

    What can I do to show this line?

     

    Thank you,

    Carlos

  • 0
    Avatar
    Josh Cohen-Collier

    Hi Carlos,

     

    Glad to hear this has been of help to you!

    We can certainly help out with that. I'd suggest a support ticket, where a support agent can go in and take a look manually.

    Would this be okay with you? If so, happy to create it for you!

     

    Best,

    Joshua

  • 0
    Avatar
    Carlos Lopez

    Hi Joshua,

    thank you for your swift reply. Yes, please, create a support ticket for me. Do you need anything else from me?

    Best,

    Carlos

  • 0
    Avatar
    Garrod Houweling

    Hey Everyone!

    Garrod here from the Klipfolio Support Team!

    Klipfolio has introduced a great new way to filter data based on a dropdown list that you can find here

    This new approach using our date filters makes it much easier to add preset date filters to a dropdown !  Please give it a try and let us know what you think!

    Carlos, as for the issue you are running into - are you currently using a SWITCH in you previous series?

    Please ensure that you are filtering in the series specifically as filtering on hidden data is applied to the whole component so it will not support 2 different timeframes

    If you are still running into issues, please do not hesitate to a create a ticket at support@klipfolio.com so we can take a deeper dive into your specific setup

    Regards,

    Garrod

  • 0
    Avatar
    Carlos Lopez

    Hi Garrod,

     

    I am not using SWITCH in my series. I just followed Josh instructions because I was a bit lost on how to create a custom date range filter, and a previous period filter. 

    I'll create a ticket.

     

    Best, 

    Carlos

  • 0
    Avatar
    Janeth Mroki

    hello, please help

    i want to pick all dates in a month and show the data of all these dates in my table,

    like if i pick january,2020, i want the table to show me all the data  from start date 1st january 2020 to end date 31st January 2020

     

    Please help, its urgent

  • 0
    Avatar
    Janice Janczyn

    Hi Janeth,

    Following the instructions and create the drop-down UIs as described above, with the following modifications.

    1. the Date Range UI control will be a list of months, for example, February 2020, January 2020, December 2019....
    2. the Start Date and End Date UI control formulas will be, respectively

                      DATE_STARTOF( DATE( $timePeriod, 'MMMM yyyy'), month)

              and

                      DATE_ENDOF( DATE( $timePeriod, 'MMMM yyyy'), month)

    Thanks,
             Janice

     

  • 0
    Avatar
    Juan Sarobe

    Hi, please help.

    i want hide both date picker ( customStart and customEnd) for custom date when i don´t use, or when the option "custom" is not selected.

     

    thanks

     

    Juan Pablo from Argentina (sorry for my inglish) ;)

Please sign in to leave a comment.