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

[kehv1n]

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 😄

[adstott90]

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.

[kehv1n]

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

[kehv1n]

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.

[Chris_Zuleeg1]

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 ;;
}

[PatKearns]

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
  }

[kehv1n]

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.

[kehv1n]

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 ;;
  }

[kehv1n]

@izzymiller what do you think boss? 👀

[izzymiller]

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.

Looker Documentation

Using Derived Tables

Define, add persistence, reference, and troubleshoot derived tables.