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:
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.
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 ;;
}
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.
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 ;;
}
@izzymiller 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.
Define, add persistence, reference, and troubleshoot derived tables.