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
