Percentage calculations and metrics with filters

1

Hi.

Is there a way to calculate the percentage of records with some condition/filter, like what is percentage of orders with a certain status vs total?

I see the way of approaching it would be to use the Count metric with a filter applied. I’ve tried to create custom metrics for this, but their filters are applied to the WHERE part instead of the SELECT of the query. I don’t know if that is a bug or not.

For example, a custom metric named “Confirmed” has this filter: status = ‘confirmed’.
Then it generates this query: SELECT COUNT(*) FROM orders WHERE status = ‘confirmed’.
When it should embed the filter in the SELECT with something like this: SELECT SUM(CASE WHEN status = ‘confirmed’ THEN 1 ELSE 0 END) FROM orders

In the second case we could just write a custom expression to get the percentage: Confirmed / Count * 100

Obviously we can make it using SQL question type, but my question is if it is posible to make it via query builder.

Thnak you for a great product, I recently discovered it and, as a developer and BI expert, will try to help you as much as I can in the future.

2

Unfortunately this is not yet possible in the GUI query builder, but it’s something we’re actively trying to find a way to enable. We recognize that calculating simple percentages and ratios like this is very common and important. The high level github tracking issue for this is here if you’d like to comment and add your support for this feature.

3

Thanks maz, it could also be addressed using virtual tables or column/dimensions. Any clue on when any of those is being scheduled?

4

Our current thinking is that virtual tables could be subsumed by the ability to use a query as a table, so it’s more likely we implement the latter first and then see if we even need the former at all.

5

Exactly, just like views, will be enough I think. Thanks again for your answer.