2

New Date Filtering - Date Drop Down / Period Selector

Hey Everyone,

In this community post, I am going to go over a specific example with our awesome new date filtering!

 

This example is a very common one, where you are looking to have a dropdown list with multiple different date ranges of different types.  So for example, having a dropdown that would allow you to select between “Last 7 Days”, “Month to Date”, “Last Quarter”,  and more


 

 

Before our new filtering, this type of filtering would require you to use a SWITCH statement to outline each case in both the X-axis and the series you were using.  Many of you will be familiar with the article on this that you can find here

 

With our new filtering, this becomes a much more simple process with significantly less formula work!

 

The first step we need to take is to add the empty bar/chart (or whichever component you would like to work with), as well as a User Input Control

 

Next, we are going to use an array function to assign our desired Labels to our User Input Control.  The Labels are going to be what is displayed in the User Input Control for the users to select.  For this example, I chose to use:

 

Now, we are going to assign the corresponding Values to the User Input Control.  The Values are what is going to be assigned to the variable when the corresponding Label has been selected.  One important thing to remember is that the order does matter as each element from both the Values and the Labels will be assigned to each other based on their place in the Array.  Also, the Values and the Labels need to have the same number of items in their arrays.  For the Labels above, here are the corresponding Values:

 

One thing that jumps out right away, and you might be confused about is “why if our first Label is “Last 7 Days” do we have a “6” as the Value?”.   This is a great question, and what we need to remember is that we will be using out “Last Full” filter option which does not include today because today has not finished and therefore is not a “Full Day”.  This is also be the reason why in our COUNT_DAYS function we are using YESTERDAY instead of TODAY.

 

Now that we have built out our User Input Controls, its time to move onto the chart!

 

The first thing we will do is populate our X-Axis of the chart.  To do this, we just need to simply select the column that contains our dates:

 

We will also want to go under the properties tab and ensure that the “Format As” dropdown is set to “Date/Time”, our “Input Format” matches the format of the dates in the data, and the “Display Format” is how we would like it to appear on the X-Axis.  For example if you would like the data to aggregate by month, your Display Format will be: MMMM

 

Next we will select the data for our series.  Again, all we need to do is select the column that our data is in.  For my example, I want to show “Sales Target” which is in column F:

 

Now that all our data has been selected, we can move onto our filtering.  To access our filter, we want to select our X-Axis in the component tree, right click on it and select “Edit Filter”:

 

Once in the filter, we will select the “Condition” tab.  Now we will start building out our filters!

 

For the first filter, we are going to select “Last Full” from the dropdown. Then we are going to select the down arrow which will give us a dropdown of all the variables associated with our account.  We want to select the variable we assigned to the User Input Control we have built.  We will then want to select “Day(s)” because with our count days function we got the relative values of each time period in days.

 

With this filter in place we have all of our data except for today because as mentioned above, today is not a full day.  Not to worry!  We will now click the “Add Condition” button to add another condition to our filter.  We then need to select “Match Any” to change this to an OR  condition because we want data that is in either the number of days our variable has specified from today OR data from today

 

And with that, we are done! Now as you change the User Input Control, it will filter the data for the time periods you laid out in your User Input Control

 

As always, if you have any questions please do not hesitate to reach out!

Garrod

7 comments

  • 0
    Avatar
    Brice Sloan

    very cool!

  • 0
    Avatar
    Mehmet Yiğit Konur

    Hi. This doesn't work on repeating days. For example

    Date                  LP

    2018-02-01 .     a

    2018-02-01 .     b

    2018-01-01 .     b

     

    How can use period selector in this kind of data?

     

    Thanks

     

  • 0
    Avatar
    Scott Lawrence

    Hi Utku,

    Thanks for raising your question in the community.

    Can you describe your situation and what outcome you are trying to achieve?  I tried a number of scenarios using the filter and your example that achieved expected results.  I may not have looked at exactly what you were trying to achieve.

    Thanks,

    Scott.

     

  • 0
    Avatar
    Angelo Reyes

    Hi All,

    How to use this filter when my period selection is "1stQuarter", "2ndQuarter", "3rdQuarter" & "4thQuarter"?

    My data table is shown like this:

    Column 1 - will be my series; then Columns 2 and so on... - are the months of the year (January to December) in MMMM yyyy format

    I was able to successfully use switch on a previous klip using UIC to control a table.

    What specific function (relative to date manipulaion) or combinations of function and filter can be used for a chart?

    Thanks for the help!

     

  • 0
    Avatar
    Janice Janczyn

    Hi Angelo,

    Assuming you're always working with current year data, you would use the DATE_UNITVALUE function to get the quarter value of a given month. Using your data for example, you dates must first be converted to Unix format (they are in MMM-yy format) before being passed into the DATE_UNITVALUE function:

         DATE_UNITVALUE( DATE( 1:1, "MMM-yy" ), quarter )

    returns blank,blank,1,1,1,2,2,2,3,3,3. Note that this returns only the quarter, regardless of year. These results can then be matched with your period selector. If your UI labels are 1stQuarter,2ndQuarter,3rdQuarter,4thQuarter, the corresponding values could simply be 1,2,3,4. 

    Note that Klipfolio functions are designed to work with columns of data so if you can re-orient your data such that the dates are in 1 column and the items in your first column become your column headers, working with the data becomes more straightforward.

    Thanks,
              Janice

  • 0
    Avatar
    John Wedderburn

    This was great - clear instructions with an explanation of why the steps are required. More please!

  • 0
    Avatar
    Garrod Houweling

    Hey John,
    Really appreciate the feedback!

    We are always looking for new content to publish in our community!

    Do you have any suggestions on articles or a specific use case you would like to see that would benefit users in Klipfolio?

    Always open to suggestions!
    Garrod

Please sign in to leave a comment.