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:
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.
The filters work as AND and that’s why you’re not seeing everything. You won’t be able to achieve that kind of conditional logic with normal filters. You may need to use Custom Filters for that..