Using Filters in Visual SQL
What you’ve now selected from the database is a smaller set of data than what is in the entire table. You may need to pare this down further. To do that you can use Filters. Using filters is essentially saying to the data source, “Please show me the data from the columns I have asked for in the measures and dimensions boxes, but I would only like it when these specific conditions are met.” The filters box is where you prescribe those conditions.
Using the Filters box is functionally similar to working with measures and dimensions. You just need to click and drag the column from the table that you want to set the conditions on to the box. Once the column item is in there, Chartio will ask you which conditions you’d like to set.
Here there will be some pre-filled information based on the column type on which you are setting the conditions. If it is a number field, it will be equals or greater than or something in that vein. If it is a text field, you can set it to filter out or filter in a certain word or set of words, strings, or even a sentence. When you are using a date field, you can set a date range for the returned data set.
In our example below we are going to use the same created date field, and the condition we are going to set is for the date span to be only this year. We can either set an exact date like 1-1-2018 or we can set a variable date. Using a variable date is beneficial for us because it will always show this year to date. Every time we open the dashboard that this chart lives on will always be from the beginning of this year through whatever date “today” is.
What is even better about this section, is that the column you are filtering on does not have to be one of the columns you are selecting in the query. You can use any of the columns in the table you are working in or any of the columns from the tables that have a foreign key relationship with the table you have already added to the measures and dimensions box.
Say, for example, you want to know how the sales are going for customers in the state of New York. By dragging the State column from the accounts table to the filters box and typing in “NY” to the filter’s conditions box you can see only sales data for New York.
You can select multiple states too. Maybe you want to see how New York and California are doing. You would need to just change the filter condition to “is one of” and add CA as one of the options.
Or you can use the two filter columns together.