Error when I put two measures in an explore

[bryan_rubi]

Hello! I recently have had problems when I tried to put two measures in an explore and the result is the following:

Here is the LookML of the two measures that I used:

dimension: is_paid {
hidden: yes
type: string
case: {
when: {
label: “Yes”
sql: case when ${doc_type} = ‘0001’ then ${bus_status_nbr} = 1
else null end;;
}
else: “No”
}
measure: paid_orders {
label: “Paid Orders”
type: count_distinct
sql: case when ${is_paid} = ‘Yes’ then ${order_no}
else null end;;
}

dimension: paid_orders_gross_amount_raw {
hidden: yes
type: number
value_format_name: usd
sql: case when ${is_paid} = ‘Yes’ then ${gross_sales_amt_raw}
else 0 end;;
}
measure: paid_orders_gross_amount {
label: “Paid Orders Gross Amount”
type: sum
value_format_name: usd
sql: ${paid_orders_gross_amount_raw} ;;
}

The explore:

Could anyone help me or guide me to the solution, please?
If it can help in something my source database is Druid

Thanks in advance!

[Hisham_Elhadidi]

Hi Bryan

Why do you use count distinct here ?

bryan_rubi:

measure: paid_orders {
label: “Paid Orders”
type: count_distinct
sql: case when ${is_paid} = ‘Yes’ then ${order_no}
else null end;;
}

I will assume that you want the new measure: paid_orders to display the Count of ALL the paid orders.
then your script should be like:

dimension: is_paid {
hidden: yes
type: number
sql: case
when ${doc_type} = ‘0001’ then 1
else 0
End;
}

measure: paid_orders {
label: “Paid Orders”
type: sum
sql: ${is_paid};
}

I hope this works with you 😬

[bryan_rubi]

the thing is that my data is at order line level, so when I do that sum as you suggested, it sums all the lines, but I need to know the count of orders

that’s why I was trying to do a count_distinct or something like that, directed to the order_no, not the order_line_no that I have

do you have any suggestions considering this?

[izzymiller]

Reposting a post that got deleted by downtime—

re: the error, this one stumped me too, and after some digging it looks like one of our own engineers actually filed a bug on the Druid repo for this exact issue, but it was resolved nearly a year ago:

github.com/apache/druid

Exact distinct-COUNT with complex expression (CASE, IN) throws NullPointerException

opened 05:41PM - 24 Jun 19 UTC

julianhyde

Exact distinct-COUNT fails at planning time if the expression is complex. In this case the expression involved CASE and IN. Here's...

Area - SQL Bug

Can you verify the version of Looker you’re running on? If it’s a recent version, then it’s possible something similar to this has re-appeared.

I think I remember you said it was a recent version, but I’m not sure

[bryan_rubi]

Hi!
Yes, we have 7.0.4

[izzymiller]

I’ve been digging around on this one, but haven’t been able to surface anything! If you haven’t resolved it, I’d recommend reaching out to support via chat or help.looker.com to get it looked at.