Odd behavior with yesno dimension

Hello!! I have a Looker report that uses a view. It relies on a derived table built thusly

view: cte_seller_revenue_past_12_months {
derived_table: {
sql: SELECT
seller.id as seller_id,
true as revenue_past_12_months
FROM ...
JOIN ...
JOIN ...
WHERE contract_entry.INCOME > 0
...
...
GROUP BY 1,2
HAVING COUNT(DISTINCT (TO_CHAR(DATE_TRUNC('month', CAST(contract_entry.OCCURRED_TIME AS TIMESTAMP_NTZ) ), 'YYYY-MM'))) = 12
ORDER BY 1 ;;
}

The LookML has two dimensions

  dimension: seller_id {
type: string
hidden: yes
sql: ${TABLE}.seller_id ;;
}

dimension: revenue_past_12_months {
type: yesno
sql: ${TABLE}.revenue_past_12_months = 'True';;
}
}

When I add my `revenue_past_12_months` as filter, it behaves oddly. If I select NO, I would expect to retrieve 0 records but it does not work (I get data back). If I select YES, I get data back.

Looking at the SQL code for the report itself, I noticed the following

WITH cte_seller_revenue_past_12_months AS (SELECT
seller.id as seller_id,
true as revenue_past_12_months
FROM ...)
SELECT
seller.SELLER_ID AS "seller.seller_id",
seller.COMPANY_ID AS "seller.company_id",
(CASE WHEN cte_seller_revenue_past_12_months.revenue_past_12_months = 'True' THEN 'Yes' ELSE 'No' END) AS "cte_seller_revenue_past_12_months.revenue_past_12_months"
...
FROM DBT.CONTRACT_ENTRY_MART AS contract_entry
LEFT JOIN DBT.COMPANIES_MART
AS seller ON contract_entry.SELLER_ID = seller.MEMBERSHIP_ID
LEFT JOIN cte_seller_revenue_past_12_months ON seller.SELLER_ID = cte_seller_revenue_past_12_months.seller_id
WHERE ... AND (NOT (cte_seller_revenue_past_12_months.revenue_past_12_months = 'True') OR (cte_seller_revenue_past_12_months.revenue_past_12_months = 'True') IS NULL)
GROUP BY
1,
2,
3
ORDER BY
1
FETCH NEXT 500 ROWS ONLY

Why is Looker adding an OR (cte_seller_revenue_past_12_months.revenue_past_12_months = 'True') IS NULL  ?? If I remove it, the filter runs successfully.

I know I am missing something here. What do I need to do to make my filter work? Any advice?

0 5 502
5 REPLIES 5
Top Labels in this Space
Top Solution Authors