LinkedIn
Twitter
I am currently working on a loss ratio report that aggregates our clients invoice amounts and claim expenditure over a given timeframe.
Here is an example of our claim table and two sample outputs:
The user is required to input both sets of timeframes. As example, a claim may look like this:
| Claim Number | Incurred Date | Paid Date | Total Paid |
| 10001 | 12/25/2020 | 01/05/2021 | $1,000 |
| 10000 | 11/01/2020 | 11/30/2020 | $500 |
If the user inputs, Incurred Date in 2020 and Paid Dates in 2020 the query should output
Total Paid: $500.
If the user inputs, Incurred Date in 2020 and Paid Dates in 2020 & 2021 the query should output
Total Paid: $1,500.
The explore consists of:
- A cross joined table consisting of each client’s group_id and dates from a calendar table
- Join Claims table on group_id and incurred date
- Join Paid Date Calendar table on the claims.paid_date field.
- Join Invoices on group id and incurred date
Full Explore:
explore: groups_sim {
from: groups
label: "Medical Loss Ratio - Claims Test"
join: incurred_dates {
from: calendar
type: cross
fields: [incurred_dates.date_date, incurred_dates.date_month]
relationship: one_to_many
}
join: claims {
sql_on: ${groups_sim.pk1_group_id} = ${claims.group_id} and ${incurred_dates.date_date} = ${claims.dos_earliest_date};;
relationship: one_to_many
}
join: paid_date_calendar {
sql_on: ${claims.paid_date_date} = ${paid_date_calendar.date_date} ;;
relationship: many_to_one
}
join: invoices {
sql_on: ${groups_sim.pk1_group_id} = ${invoices.pk2_group_id} AND ${incurred_dates.date_date} = ${invoices.billing_month_date};;
relationship: one_to_many
}
}
Here is an example of the what we would expect to see:
When I add the paid date filter to the explore, the resulting table filters out Jan & Feb 2020 and filters out Invoice data from Aug and Nov.
Any help with this will be greatly appreciated.
