The CreatorCon Call for Content is officially open! Get started here.

State Duration Breakdown issue for KPI trending

ThorS
Tera Contributor

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! ❤️

 

0 REPLIES 0