Creating a new Filter Variable

 > Filters > Creating a new Filter Variable

You can create a global filter variable to affect your dashboard by changing dynamically the data that is used across a dashboard, filtering it for a particular value or date range.

To add a filter variable to the dashboard you must create one that can be added to each widget that you want to be "filtered".

After created, these filters variables are located right under the navigation bar on the dashboard.

global filter

To create a global filter variable to your dashboard click the dropdown button on the top bar then click on Global Filters.

You will be brought up the menu, that shows the list of all global filters affecting the current dashboard.

On the All Variables tab click on the "Create a New Variable" button.

Tip
Watch on youtube:
Creating a Data Range Filter Variable
Creating a Text Filter Variable from SQL

There are four types of filter variables:

 

Note
After you create a global variable on the section below, you have to assign them to your widgets, learn to do that step in Use Variables in Widgets page.

 

Text Input Filter Variable #

Text Input Filter Variable is used when you want to filter values by directly typing them in the search bar, it can only be used with numerical or text columns.

To create a Text Input Filter Variable filter you need to define the following information:

  • Name: Name of the filter variable.
  • Data Type: Choose if you want to filter numerical or text data.
  • Multiple Values?: Select this option if you want to filter more than one value at the same time, separated by commas ",".
  • Default Value: Default value of the filter.

Text Input Filter

 

Dropdown Filter Variable is used when you want to filter values that are only present in a column, it can be used with numerical, text or boolean columns.

To create a Dropdown Filter Variable you need to define the following information:

  • Name: Name of the filter variable.
  • How do you want to get the values for this dropdown?: this is where you define the attribute that will be "listening" to the parameters that are chosen when on the dashboard (visualize an example in the first image this page).
    • Column: You can select it from a single dropdown selection by choosing the respective table and column.
    • SQL Query: Or be inserting the SQL query yourself (this method have some available useful tricks).
    • How to select values with SQL;
  • Show all data when empty?: When no value is selected you can choose to show all data (no filter) in the connected charts or show no data until values are selected.
  • Allow to select multiple values: Select this option if you want to filter more than one value at the same time.
  • Initial Value: Default value of the filter.

Dropdown filter

 

How to select values with SQL #

Create with SQÇ

Tip
You can test your query by clicking on the Run button

This example shows how you can add a global variable to filter by countries:

SELECT "Country"
FROM "Suppliers"
GROUP BY "Country"

To use variables in the code editor simply use the variables with the curly brackets {}.

---Example with text variables:
SELECT COUNT("Customers"."CustomerID") AS "Customers"
FROM "Customers" 
WHERE {"Customers"."Country" = Countries} 
---Example with dates:
SELECT CAST("Orders"."OrderDate" AS DATE) ,
COUNT("Orders"."OrderID")  
FROM "Orders" 
WHERE CAST("Orders"."OrderDate" AS DATE) 
BETWEEN {OrdersDate.start} AND {OrdersDate.end} 
GROUP BY DATE_FORMAT("Orders"."OrderDate","%Y-%m-01") 
ORDER BY DATE_FORMAT("Orders"."OrderDate","%Y-%m-01") ASC 

 

Note
After you create a global variable, you have to assign them to your widgets, learn to do that step in Use Variables in Widgets page.

 

Date Range Filter Variable #

Date Range Filter Variable is used when you want to filter dates, it can only be used with date columns.

To create a Date Range Filter Variable filter you need to define the following information:

  • Name: Name of the filter variable.
  • Default Period: Since this is a date filter, here you define the period that it will filter upon, this can be:
    • Today
    • Yesterday
    • This Week
    • This Month
    • This Year
    • Last Week
    • Last Month
    • Last Year
    • Last 7 Days
    • Last 30 Days
    • Between Fixed Dates
      • Starts at: defines the start date.
      • Ends at: defines the end date.

Date Range

 

Note
After you create a global variable, you have to assign them to your widgets, learn to do that step in Use Variables in Widgets page.

 

Date Aggregation Variable #

Date Aggregation Variable is used when you want to aggregate date ranges, it can only be used with date columns.

Viur already has by default one Date Aggregation Variable created on the global filters, if you need to use it you can just assign it to the dashboard, or if you prefer you can create more variables of date aggregation.

To create a Date Aggregation Variable filter you need to define the following information:

  • Name: Name of the filter variable.
  • Default Period: Since this is a date filter, here you define the period that it will filter upon, this can be:
    • Minute
    • Hour
    • Day
    • Week
    • Month
    • Quarter
    • Year

Date Aggregation

 

Note
After you create a global variable, you have to assign them to your widgets, learn to do that in Use Variables in Widgets page.

Still need help? Get in touch!
Last updated on 16th Oct 2018