Dear visitor, despite the COVID-19 outbreak, our team continues operating at full speed.

Also, here is the form where you can apply for a special discount and we will contact you with possible options. Stay safe and continue achieving your business goals.

Fill the form
Get Free Trial
Get Free Trial

Is it possible to filter by the result of an aggregation?

Answered
Andrew asked on April 20, 2020

I’m generating a report that aggregates a value. There are a lot of results and I’d like to show only the top X items (top 10 for example).
Ideally this would be possible to configure via the user interface as this is for a client who I implemented Flexmonster for their own custom reports
Here’s the report object (generated by Flexmonster)
“report”: {
“slice”: {
“sorting”: {
“column”: {
“type”: “desc”,
“measure”: {
“aggregation”: “count”,
“uniqueName”: “dateCreated”
},
“tuple”: []
}
},
“rows”: [
{
“uniqueName”: “userCompany”,
“caption”: “User Company”,
},
{
“uniqueName”: “projectName”,
“caption”: “Project Name”
},
{
“uniqueName”: “userName”,
“caption”: “User Name”
}
],
“flatSort”: [
{
“uniqueName”: “dateCreated”,
“sort”: “desc”
}
],
“columns”: [
{
“uniqueName”: “[Measures]”
}
],
“measures”: [
{
“availableAggregations”: [
“count”,
“distinctcount”
],
“aggregation”: “count”,
“uniqueName”: “dateCreated”,
“caption”: “Date Created”
},
{
“active”: false,
“availableAggregations”: [
“count”,
“distinctcount”
],
“aggregation”: “count”,
“uniqueName”: “projectName”,
“caption”: “Project Name”
}
]
},
“options”: {
“chart”: {
“title”: “Systems per company”
},
“viewType”: “charts”,
“grid”: {
“title”: “Systems per company”
}
}
}
 
Thanks

2 answers

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster April 24, 2020

Hello, Andrew,
 
Thank you for your question.
 
The desired result can be achieved through filtering.
It is possible to adjust an appropriate filter, both using UI and by default/programmatically.
For example, in case manual filtering is required, it is possible to open the pop-up dedicated to filtering (gear icon on the hierarchy’s header). Next, the “Values” button should be pressed. You will be proposed to choose from the set of possible conditions (to see the top elements, choose the “Top” condition), and specify the number of top elements you would like to see (10).
Please refer to the screenshot attached.
 
In case such filtering needs to be applied by default, it can be defined in the following way:

{
"uniqueName": "Color",
"filter": {
"measure": {
"uniqueName": "Price",
"aggregation": "sum"
},
"query": {
"top": 10
}
}
}

 
The object presented above is an element of the rows or columns array, which are properties of the slice object. The value of the filter property is a Filter Object, which contains the unique name of the measure the filtering should be based on, its aggregation, and the Value Query Object, which serves as an actual condition.
 
You are welcome to find out more about the Filter Object and its properties, including the Value Query Object, in our documentation.
 
We hope it works for you.
 
Kind regards,
Illia

Attachments:
Untitled.png

Public
Andrew April 24, 2020

Thank you, that’s perfect, I’m not sure how I missed that!

Please login or Register to Submit Answer