1

How do I set up a date selector with different date ranges to view the data in my Klip?

This is a favorite Klip of mine.  Why?  Because the date selector let's you quickly see a different view of your data, plus it's easy to build and maintain over a period of time. 

The date ranges in my example klip are:

  • This Month
  • Last Month
  • This Year
  • Last Year
  • All Time

To set up the date selector, you will need to drag in three User Input Control components.

User Input Control 1:  Use the ARRAY function to associate a date range with a number.

Create a variable called daterange

For the Values sub-component use this formula.
ARRAY(0,1,2,3,4)

For the Labels sub-component use this formula.
ARRAY("This Month","Last Month","This Year","Last Year","All Time")

This means 0 is associated with This Month, and so on.

 

User Input Control 2: Use the SWITCH function to specify a start date for each date range selection.
For the Values sub-component

SWITCH(

$daterange,

0,

DATE_STARTOF(TODAY(), "3"),

1,

DATE_STARTOF(TODAY(), "3", -1),

2,

DATE_STARTOF(TODAY(),"1"),

3,

DATE_STARTOF(TODAY(),"1",-1),

4,

MIN(DATE(<your date column>,"dd-MMM-yy")

)

)

To help with building your Klip, you can copy and paste the above formula and then make the below amendment.  

For case 4, you will need to replace <your date column> with the date column in your data source and specify the right date format, if it is not "dd-MMM-yy".  If you are unsure of the correct date format, you can review our ‘How do I use Date/Time formats’ article. 

This formula turns the dates into an epoch number and then brings back the smallest number/epoch date.

 

User Input Control 3: Use the SWITCH function to specify an end date for each date range selection.
For the Values sub-component

SWITCH(

$daterange,

0,

DATE_ENDOF(TODAY(), "3"),

1,

DATE_ENDOF(TODAY(), "3", -1),

2,

DATE_ENDOF(TODAY(),"1"),

3,

DATE_ENDOF(TODAY(),"1",-1),

4,

MAX(DATE(<your date column>,"dd-MMM-yy")

)

)

For case 4, you will need to replace <your date column> with the date column in your data source and specify the right date format if it is not "dd-MMM-yy".  If you are unsure of the correct date format, you can review our ‘How do I use Date/Time formats’ article.

This formula turns the dates into an epoch number and then brings back the largest number/epoch date

For User Input Control components 2 and 3, select the Properties panel, and set the Visibility to Hide Always.


Now that you have the date range selector all set up, we will build our Klip using a data source with a date column and a profit column of numbers.

Drag in a Bar/Line chart

For the X:Axis:
Select your date column from your data source.
Select the three vertical dots alongside the X:Axis and select Group to return one unique instance of the date.

To associate our start and end date variables with our Klip we will need to add a filter to the X:Axis using the three vertical dots.
The filter's condition will say:
On or after your variable called StartDate
select +Add Condition 
On or before your variable called EndDate

 

For the Series called Daily Profit
Select the profit column from your data source.

For the Series called Total Profit
Add another series and use the CUMULATIVE function and a Results reference to the Daily Profit column to sum the numbers for each day.

To learn more about working with Results references check out our how to article.  

 



 To learn more about our functions including the DATE_STARTOF and DATE_ENDOF functions check out our Klipfolio functions article.

0 comments

Please sign in to leave a comment.