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
