Open navigation

How to use Filters and Queries in Charts

Modified on Wed, 08 Feb 2023 at 03:35 PM

When creating a chart in Aico, there are two key concepts to keep in mind – those of filters and queries.

Filters limit the data, and queries divide the data. 

There is a hierarchy within Aico dashboards applying to both lists and charts. Global Filters sit at the top and affect the whole page, for example, include only Journals. Chart-level filters come next, allowing you to only see, for example, documents that are waiting for approval, since you have already set a page-level filter of Journals then you will only see journals waiting for approval on that specific chart. Finally, you can further subdivide those documents using queries, for example, by adding a query to divide the data by risk, you would see Journals, waiting for approval divided into the different risk types.

You can then use Queries to divide these documents that are waiting for approval into risk categories, for example. Then you can see which of your “waiting for approval” journals fall into high, medium and low risk.

In brief, all the documents in this chart will:

  • All be journals from the page filter.
  • All be waiting for approval from the chart filter.
  • All be subdivided further into risk groups.

Using Queries, especially in conjunction with Filters, gives you an incredible amount of flexibility when it comes to seeing specific data.

As page-level Global filters are already explained in Global filters, below we will describe how our chart-level filters and queries can be set up.

Adding a chart-level filter

Using existing queries

Creating your own queries

Adding a chart-level filter

If you want, you can add a filter to the chart. In this chart, for example, let's filter it to only look at Completed tasks.

1. Click on the + to the right of Filters

2. Click on the cog wheel of the filter box that appears.

Give the filter a meaningful name. Click on the Add button. Add your condition, in this example Workflow Status equal to Completed and then press Save.

Save the entire dashboard. This chart will now show only closing tasks that have been completed.

Adding a query

Using existing queries

Queries divide data. In the example below, we will work with the closing dashboard, dividing our completed closing tasks into risk by company.

It is important to remember that, as we are creating a new chart from within the Closing Dashboard, all results have already been filtered to show only closing tasks at the dashboard level. You can read how to set up filters on a dashboard here.

We now want to divide those closing tasks by company and risk at the chart level to create the chart below:

If you are unsure how to set up a chart, click here. This article will look only at setting up the queries to subdivide the data. We start with the basic setting of the chart to count the number of closing tasks for each company (1).  We have already set the filters to limit this to only Completed tasks.

We now want to split this data into risk, as it relates to each company. Depending on how the Aico system has been set up in your company, you will have Headers that relate to risk, which will have been assigned to each task, in this case, closing tasks.

Click on the + sign next to Queries (2).

Click on the cog wheel to set your new query.

We will choose, in this example, from the Risk Headers from the New query drop-down menu (3).

If you click on the cog wheel to the right of the query and then click on the drop-down arrow, you can see Risk has three values - High, Medium and Low (4).

Each query also has a name (5). This is the name that appears on your chart legend for each category.

You can also assign each query a colour (6).

When you have set up your query, click the refresh arrow in the top right to save the settings.

You can see when you look at your chart, the query name fields and the colours you chose now make up the legend at the bottom of the chart, as highlighted below.

To save this chart, click Save at the top left of the entire dashboard.

Creating your own queries

You may want to create a chart using data that does not have its own Header fields.

For example, when you can create your own queries, you have even more flexibility to subdivide your data, allowing you to construct an alternative view of the data using criteria that are important to you.

Let's take the below chart as an example:

This chart is concerned with the workflow status of closing tasks by due date.

Four subdivisions have been queried:

  • Not started
  • Started
  • In approval
  • Completed

There are no fields in the Aico system with any of the above workflow status except Completed. The other three exist only in these queries.

To create your own query, click on the cog wheel in the top right of the dashboard. This will take you to the settings area for this chart. Then click on the Queries + sign as outlined above.

Give your new query a meaningful name (1).

Choose the workflow status you want to display in your chart. In this example, we are considering any task that is in the Created (2) or Waiting for preparer (3) status as Not Started.

Choose a colour to represent this query and save it.

Repeat the above steps with each additional query. For example, those documents we considered to have been started have a workflow status of In preparation, so we set this query up as in the image below:

Once you have constructed your queries, Refresh the chart and save as above.

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select atleast one of the reasons

Feedback sent

We appreciate your effort and will try to fix the article