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

Good evening looker fam! :call_me_hand: :surfing_man:

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 :racehorse:)

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) :slight_smile:

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 :smile:

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.

2 Likes

Adam,

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
  }

Thank you both @Chris_Zuleeg1and @PatKearns for your descriptive and helpful feedback.

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

Will update everyone with the results.