# Is Counting a Boolean (Yes/No) Measure possible?

Good evening looker fam!

I am currently trying to find a way to display and visualize the aggregate/total counts of individuals with payments due.

There is a look with column measures that indicate the account balance due for that individual. I simply want a way to visualize the two totals:

• How many individuals have still have dues? (invoice_balance > 0)
• How many individuals have paid in full? (Invoice_balance = 0) (or even invoice_balance <= 0 since our data is a bit on the wild side )

The look is as follows:

I am not sure if this is a lookerML modeling issue or a visualization issue or both.

The snippet below is from the view that joins to the looker model (invoice ledger)

``````measure: invoice_balance {
type: sum
sql: \${TABLE}.AMOUNT ;;
}

measure: has_dues {
description: "Does the individual have dues that must be paid?"
type: yesno
sql:  \${invoice_balance} > 0;;
}
``````

I am not able to use a count measure here to count the (Yes/No)'s of has_dues because a measure cannot be based off another measure.

I then tried removing the has_dues field and simply creating a count_distinct with sql: that checks CASE (WHEN invoice_balance > 0) then 1 ELSE 0 . Unfortunately that gave me the same issue as above because I cannot create one measure off the other.

My final attempt was trying to create a table calculation from the front (not lookerML) and that almost worked but I was not able to do the counting with conditionals (count if yes). The calculation would simply count all individuals regardless of yes/no twice for some reasonâ€¦

`count_distinct(\${ar_rct_ledger.invoice_balance})`

I am confused and wondering what the best practice for something like this is. Iâ€™d simply like to have to column totals that the user can drill into and see the addends.

Thank you & Cheers

I personally wouldnâ€™t be approaching this basing it off the measure. For me, assuming you have a dimension based off \${TABLE}.AMOUNT (letâ€™s call it â€śamountâ€ť for this example), then iâ€™d look at having the following:

dimension: amount {
type: number
value_format_name: gbp (or whichever currency you have)
sql: \${TABLE}.AMOUNT ;;
}

dimension: has_dues {
description: yesno
sql: CASE WHEN \${amount} > 0 THEN true ELSE false END ;;
}

measure: invoice_balance {
type: sum
sql: \${amount} ;;
}

measure: invoices_outstanding {
type: sum
sql: CASE WHEN has_dues = true THEN 1 ELSE 0 END ;;
}

This should basically answer both of your questions by giving you a total number of invoices which still have dues (in this case it is grouped by customer, but then you could use this on a general level to do a count of how many invoices have been paid or are still due by using filters etc). You might want to do it as sum_distinct if you potentially could have duplicate invoice idâ€™s but assuming these are individual then this is the approach Iâ€™d wanna use.

3 Likes

Thank you so much for the descriptive reply.I have not considered adding BOTH dimensions + measures. Generally when we are piping a field over from EDW we ask ourselves if that field ie invoice_balance is a measure OR a dimension.

I will reply with the status of the implementation.

Thank you again for your time,

K

The current implementation of the invoice_total, invoice_balance, and invoice_payment fields are as follows:

``````  measure: invoice_total {
type: number
sql: DECODE (\${TABLE}.line_ty, 'INVLINE', \${TABLE}.amount, 0);;
}

measure: invoice_payment {
type: sum
sql: DECODE (\${TABLE}.line_ty, 'PAYMENT',  \${TABLE}.amount
ALLOWANCE', \${TABLE}.amount;;

measure: invoice_balance
type: sum
sql: \${TABLE}.AMOUNT ;;
``````

I believe there is a one-to-many relationship between an individual and his invoices. So i may end up converting most of these measure fields to dimensions if that is best practice.

We count booleans (Yes?No) by coercing them to intâ€™s and summing them, which is just a slightly different variation from Adam Stottâ€™s suggestion.

In your case, it would look like:

dimension: amount {
type: number
value_format_name: gbp (or whichever currency you have)
sql: \${TABLE}.AMOUNT ;;
}

dimension: has_dues {
type: yesno
sql: \${amount} > 0 ;;
}

measure: invoice_balance {
type: sum
sql: \${amount} ;;
}

measure: invoices_outstanding {
type: sum
sql: \${has_dues}::int ;;
}

1 Like

We do something similar, but use filtered measures:

``````  dimension: in_service_level {
type: yesno
sql: \${TABLE}.call_type = 'Inbound' AND (\${TABLE}.duration_in_queue <= 20 OR \${TABLE}.duration_in_queue is NULL) ;;
description: "Inbound-specific calls answered within 20 sec"
}

measure: num_in_sla {
type: count
label: "Number of Calls in SLA"
filters: {
field: in_service_level
value: "yes"
}
}

measure: percent_in_sla {
label: "Percent in SLA"
type: number
sql: \${num_in_sla}::numeric / \${calls} ;;
description: "Inbound calls answered within 20 sec"
value_format_name: percent_1
}
``````
1 Like

I will be testing both versions of the implementations above Monday (07/22).

Will update everyone with the results.

2 Likes

Hey guys,

So I tried both implementations mentioned above with a filtered measure and just a regular olâ€™ measureâ€¦

The issue is that the AMOUNT column in the view is a measure not a dimension like the two implementations.

The reason it is a measure, is because it invoices are complicated and have many line items per 1 invoice.

When I convert my AMOUNT column to a dimension, there is replication (ie Jimmy has dues of 14 for x and 25 for y and 30 for z) etc.

That is why it must be summed up before being taken to the screen (If that makes any sense). Iâ€™m thinking I may have to simply do the summations in the DW before being taken to Lookerâ€™s Views.

Please let me know if anyone can come up with any better ideas but that may be the route to take if notâ€¦

Pasting my lookML for reference:

``````     measure: invoice_total {
type: sum
sql: DECODE (\${TABLE}.line_ty, 'INVLINE', \${TABLE}.amount, 0);;
}

measure: invoice_payment {
type: sum
sql: DECODE (\${TABLE}.line_ty,
'PAYMENT', \${TABLE}.amount,
'ALLOWANCE', \${TABLE}.amount,
0) ;;
}

measure: invoice_balance {
type: sum
sql: \${TABLE}.AMOUNT ;;
}``````

@izzy what do you think boss?

Love all the ideas and methods in this thread! For your case, Kevin, since the thing you want to filter on is a measure, I think youâ€™re correct in saying that you need to do the summations at an earlier stepâ€” But that doesnâ€™t necessarily need to be in the DW.

You could take this to a Looker derived table, and do your has_dues calculations in the derived table, returning it as a dimension that you can sum/count/filter on.