Expression for count if the average is greater than target number

[#1]

I'm trying to create expressions for a KPI object based off information shown in this chart:

This is the average time it takes to complete a Priority 3 Work Order, by Trade, with a reference line showing where 6 months is, as the average time should be less than 6 months. I'm been tasked with created a KPI object showing:

1) % of Trades Meeting this Target [Ex: 18/21 = 86%)

2) # of Trades Meeting this Target [Ex: 18]

3) # of Trades not Meeting this Target [ Ex: 3]

I believe I'm going to need to create three expressions, and therefor three measures using the KPI chart.

I started so far for 1 tried to create an expression like:

Count(Aggr(avg({$<[PRIORITY]={'3'},[DAYSTOCOMPLETE]={'>-1 and <=186'}>}DAYSTOCOMPLETE)/

avg({$<[PRIORITY]={'3'},[DAYSTOCOMPLETE]={'>186'}>}DAYSTOCOMPLETE)),[WORK_TEAM_ID]))

...but it's invalid. Basically I need to determine if a trade is coming in under or equal to the target for days to complete a work order, and then the count of those trades , the count of if they aren't meeting the target, and the percent that are meeting the target.

Thanks for any help!

[#2]

Thanks! Where would I put the if(PRIORITY=... [Trade Met Target Flag] in the script?

[bobdawes]

Ultimately it depends if you have multiple records per WORK_TEAM_ID, requiring you to calculate the avg(DAYS_TO_COMPLETE) first.   In general, averages can be misleading, especially when you consider taking average of averages, as you lose the weighting of more significant records.

If you don't have multiple records per WORK_TEAM_ID, you can create the Trade Met Target Flag right when you load your facts, comparing the PRIORITY and DAYS_TO_COMPLETE.

If you do have multiple records per WORK_TEAM_ID, then you must first aggregate the avg(DAYS_TO_COMPLETE) and get that attached to the Fact table, likely by using the Temp table to aggregate, and then using a LEFT JOIN to attach the result.

The easiest approach is load a Fact-version2: table from the original Fact, and then you have the PRIORITY and AVG DAYS TO COMPLETE fields on the same record, so you should be able to do the IF logic then.

Then remember to drop the Fact-version1 table.   You'll likely want to rename Fact-version2 back to the original Fact table name, just in case you reference downstream in the script.

There are more efficient ways of accomplishing this, but as long as your load times are reasonable, going through through the interim tables will help you understand the ultimate data model.   Use Exit Script statements after building the temp table if you want to validate the results first.   Just be careful of synthetic keys, so save early and often....

[#4]

There are multiple records per work team. For an entire year, "Electrician" is called out numerous times.

If I decided not to create the flags as to not have to create more temptables, could I just specify "[AVGDAYSTOCOMPLETE]={'<1'}" in the expression?

[bobdawes]

OK, I've been missing that "Trades" are an attribute on your fact table, which consists of Work Orders.  Apologies. 

Your challenge is that the set analysis is applied against the rows in the table prior to the calculation, meaning you can't look to calculate the average first, and then use set analysis against the result to compare to the target.

Will need to play with "sum if" relationships, and consider if creating a "Trade" level entity in the data model which precalculates the average days to complete would be possible.  The challenge will be linking it to the calendar table, or the any other dimensions like "product", etc.   My guess is the data model is not the answer.

[#6]

Thanks. I will have to keep looking then.