hi, wondering if fact in where clause works. Created a metric something like select count(customer_id) where sales_rank=1. sales_rank is a fact, numeric type. seems this metric doesn't work on the dashboard applied with a filter. The count(customer_id) is computed regardless of the value of sales_rank.

Hi Jing, facts do work with the WHERE clause, but this would certainly depend on your metric syntax in the end. For example, could you speficy what is “sales_rank”? is it an attribute or metric? Also, are you using GoodData Cloud?

sales_rank is a fact in dataset customer. Gooddata cloud.

If you wish to make it work with the fact filter, then it would be necessary to drop the COUNT function since it won’t be able to count unique records in facts, it can only work with attributes, in other wods, COUNT is only <counting> the unique records in customer_id without taking into account the values in sales_rank. With that in mind, you could define your metric as

SELECT SUM (sales_rank) WHERE (SELECT SUM (sales_rank) BY customer_id) = 1

and add the count metric to the insight separately.

@Moises Morales do you mean

SELECT COUNT(customer_id) WHERE (SELECT SUM (sales_rank) BY customer_id) = 1

? the goal of this metric is to count customer_id with sales_rank=1. My use case is that each customer_id may have multiple sales_rank. example dataset : customer_id sales_rank date abc 1 5/1/23 abc 2 5/2/23 abc 3 5/3/23 cde 1 6/1/23. Could you elaborate the computation of

(SELECT SUM (sales_rank) BY customer_id) = 1

does it select all customers with sales_rank=1? what is the logic behind it?

The metric SELECT SUM (sales_rank) WHERE (SELECT SUM (sales_rank) BY customer_id) = 1 would act as a filter wherein the fact is filtered as 1, but you would need to break it down by the intended attribute so it does not show the row total as it’s simply aggregating the sales_rank by the attribute (hence the SELECT SUM…). You could then count the unique attributes by adding the metric SELECT COUNT (sales_rank) and sum all the rows:

Hi Jing, if you already have

sales_rank

in your data as a fact, the filter for such fact should work both in GoodData Cloud and GoodData Platform. It will only work within a MAQL metric (i.e. not as a dropdown filter) and in case of GoodData Platform, the computation performance might be a bit worse than if it was an attribute (in GD Cloud it depends how you index your tables). It will also only work if WHERE part is structurally applicable to the metric after SELECT in that particular LDM, but if I understood it correctly, you have both the

customer_id

and

sales_rank

in the same dataset, so that should be the case and something like

SELECT COUNT({attribute/customer_id}) WHERE {fact/sales_rank}=1

should work. And in my simple test it worked for me. Is it a simple metric like this that is not working for you or something more complex that also involves other datasets? And what is the data type of the fact in your database? Can’t something like

WHERE {fact/sales_rank}=1.0

help? Lastly - is the sales_rank really recognized as a fact by GoodData (i.e. does it have green “123” icon in the Analyze tab)? I can imagine if it was for some reason an attribute and in GD Platform you would use it in a filter and manually wrote a number after the “=” and not selected it from the value list, such filter would get ignored. But here I am really just guessing what might be wrong.