State Duration Breakdown issue for KPI trending

[ThorS]

Having an issue with what I believe is a combination of things and could use help diagnosing for a solution:

At play are:

1. A Metric Definition (and associated instance table) to capture the duration a record has been in particular option/label/selection within the State field.
2. A custom table extended from the Task table that has the State field being duration(ed?) which is a choice field referencing the sys_choice table.
3. A database view that joins the metric_definition, metric_instance, and custom tables showing the duration values per State option/label/selection per record per change. 
   • So if you went from State 2 to 3, and then back to State 2 and jumped to 4 (complete/done/end), you'll see 2 line entries for that model for State 2. One showing the dates entered/exited that state the first time and another for the second time with States 3 and 4 only getting 1 line a piece. 
4. Custom App Scope?
   • I've seen a post that this shouldn't matter, but thought it may be worth mentioning since it was one thing that stood out as different from the labs in the On-Demand Course. 

I've found other related questions on breakdowns, and have gotten the Indicator scores to breakdown properly by State option/label/selection when using a value on the custom table itself.

The issue has come up when trying to setup an Indicator that focuses on the State duration values which are not "local" to the custom table. 

So far I've done the following:

• Indicator Source; Facts Table = Database view
• Indicators (2)
  • Source = above; Breakdowns = CT_State ; Average durations per state. 
  • Source = custom_table ; Breakdowns = CT_State; Sum of values from "native" custom_table field
• Breakdown Source; Facts Table = Choice [sys_choice] ; field = sys_id + applicable conditions where the "Preview" displays all expected values.
• Breakdown CT_State; Source = above ;
  • Mappings (2)
    • Facts table = custom_table; Field = state
    • Facts table = database view; Field = mi_value
  • Indicators - the 2 from above.

So the indicator scores for the "Sum of values from "native" custom_table field" gets broken down by the different States just fine.

However the Average durations per state indicator scores all end up getting assigned the "Label for unmatched".

I've exported the data from the choice table, the custom table, and the database view to check for any odd formatting issues - and have found none. All values match across the 3 tables for the fields/columns reference in each.

Any help you can provide is appreciated! ❤️

UPDATE:
A couple of things came to light today:

1 - I was able to get the scores to distribute by State breakdown - but not in the way we need.  So in the database view - we're pulling in the "Value" field/column from the metric_instance table which has the State value along with the associated duration value. We're also able to pull in fields from the custom_table record, one of which is the State field. So in the database view the 2 columns (Value and State) contain all of the same kinds of values (WIP, Draft, Completed, etc.) If you setup your breakdown mapping to use the database view as the facts table and select the State field (which ends up showing with a prefix with the custom table abbreviation) - then the breakdown distributes the scores. But it's doing so based on the current State of the record - not each respective State value the record was in until it was closed. 

But if you setup the breakdown mapping to select the same State field from the custom_table, but instead using the custom_table as the facts table - it won't work. 

So something about trying to use that field through the database view to the metric_instance table is causing an issue because we can go through the database view to another field with the same values in it from the custom_table without any issues. 

2 - Is it the fact that we're even using a database view that's causing the issue? Or maybe it's how we're setting up the view?

Database View:   Application: Custom App 

Table = metric_definition ; Var prefix = md ; Where clause: md_table = 'x_custom_table'

Table = metric_instance ; Var prefix = mi ; Where clause: mi_definition = md_sys_id

Table = x_custom_table ; Var prefix = ct ; Where clause: mi_id = ct_sys_id

[ThorS · Accepted solution]

So apparently the fix is to map mi_field_value instead of mi_value for some reason within the breakdown mapping.

Anyone know why?