Group other and missing in Canvas piechart like in Normal Kibana view

In a normal Kibana PieChart(and many other views) you typically do a top N bucketizing. You have the option to show the percentage of missing and the number of results that fall outside the top N.

How do I do that with PIE charts in Canvas, using SQL. If I use LIMIT it will just give me the top N results?
In the below query I exclude the missing and only get the top 5 results.
I care less about the missing but I would like to group the missing ones in an "other" group.
The Pie chart does not support that. But maybe Elastic SQL does?

SELECT Count(identifier) as count, 
from document.CountryName.keyword as Country
FROM "ourlogs-*"
WHERE Country is not null
group by Country
order by count DESC limit 5

Anyone has a suggestion on how to do that?

Hi @Hans_Kruse,

this is currently not possible. Regular Kibana visualizations get the "other" group by doing a second request which you can't really express with SQL.

However there is currently work going on (planned to be released with 7.5) to make it possible to embed regular visualizations into a Canvas workpad which would also solve this problem.

1 Like

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.