14 Replies Latest reply: Jun 9, 2017 8:03 AM by Casey McDonald

# Expression for count if the average is greater than target number

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!

• ###### Re: Expression for count if the average is greater than target number

Can you share the expressions of your chart? Both the expression for bar and the 6 month reference line

• ###### Re: Expression for count if the average is greater than target number

Reference line is: =365

Chart is: Avg({\$<[PRIORITY]={'3'}, STATUS_GROUP={'Closed'},[DAYSTOCOMPLETE]={'>-1'}>}DAYSTOCOMPLETE)

• ###### Re: Expression for count if the average is greater than target number

First, I'd arrange to add a field to your data model that will flag each fact (trade) met the target or not.  This will simplify your set analysis.   You should also have a counter field on the trade fact with a 1 for each unique trade, allowing you to sum the counter field rather than use a count() function.

Your example formula likely doesn't work.  It's first doing the average days to complete for the 18 trades that met the target, dividing by the average days to complete for just the 3 trades that did not meet the target, and then it's counting how many unique averages you have for each work team ID.  My guess is the result is the number of work teams.

I'm not sure why you need to aggregate by Work Team ID.    If you don't need the aggregation, the formula might be something like:   count({\$<[PRIORITY]={'3'},[DAYSTOCOMPLETE]={'>-1 and <=186'}>} TRADE ID)/

• ###### Re: Expression for count if the average is greater than target number

Adding a flag in the data model as to whether or not the target was met wouldn't work because this table is based on a filter of year. It will change depending upon the time frame filtered, as will if they met the target or not.

Each trade has a unique name [WORK_TEAM_ID]. It's not a number, however.

I don't know if the aggr() function was needed, I was just trying different things. But yea, my expression was dividing only by 3trades, it would need to be total trades.

The formula you suggested wouldn't give me what I need. It would give me a count of trades where the days to complete is less than  the target, but I need to use the average days to complete and compare that to the target, and then a count of the trades where their average days to complete falls under the target.

I can't hard code in to count a total of trades because there were different trades for different years, so it would be off. I essentially need an expression for this:

[Count of Trades Whose Average Number of Days is <=Target]

[Count of Trades Whose Average Number of Days is <=Target] + [Count of Trades Whose Average Number of Days is >Target]

And expressions for:

[Count of Trades Whose Average Number of Days is <=Target]

[Count of Trades Whose Average Number of Days is >Target]

Does that help?

• ###### Re: Expression for count if the average is greater than target number

So I don't really understand the target.   The target varies by year?     Or the target varies based on the dates selected in the application?   In your example, the target was hardcoded to be <= 186.

Other question:    Can a given trade have multiple days to complete that need to be averaged?

• ###### Re: Expression for count if the average is greater than target number

No the target varies based on Priority. It won't change and there's only 4 priorities. Priority 1 <1 Day, Priority 2<2 Weeks, Priority 3<6 Months, Priority 4< 1 Year.

That won't change.

Other question: I don't understand. DAYTOCOMPLETE is a field that provides the number of days between completed and created date, per work order. Each work order also has an associated trade.

• ###### Re: Expression for count if the average is greater than target number

So the next thing to verify what "Average Number of Days" is for a trade.    It suggests that you have multiple "DAYS_TO_COMPLETE" for a single trade.  Is that right?

If so, I'd consider creating a temporary table in your data model at the Trade level of detail along the lines of:

WORK_TEAM_ID

, avg(DAYS_TO_COMPLETE) as [Avg Days To Complete]

Resident FactTable

then move the Avg Days to Complete back to the Fact table:

Left Join (FactTable)

[Avg Days To Complete]

Then clean up the data model by dropping the TempTrades table.

You could further enhance the scripting to set a "Met Target Completion Days" Flag along the lines of:

if(PRIORITY=1 and [Avg Days To Complete]<=1, 'Y',

if(PRIORITY=2 and [Avg Days To Complete] <=14, 'Y',

if(PRIORITY=3 and [Avg Days To Complete] <186,'Y',

if(PRIORITY=4 and [Avg Days To Complete] >365.25,'Y','N')))) as [Trade Met Target Flag]

Does this make sense?

If it does make sense, I think your expressions would be defined as:

[Count of Trades Whose Average Number of Days is <=Target] = count \${<[Trade Met Target Flag] = {'Y'}>}([WORK_TEAM_ID]

[Count of Trades Whose Average Number of Days is >Target] = count \${<[Trade Met Target Flag] = {'N'}>}([WORK_TEAM_ID].

[Average # of Days for Trades that Met Target] = avg(\${<[Trade Met Target Flag] = {'Y'}>} DAYS_TO_COMPLETE)

• ###### Re: Expression for count if the average is greater than target number

That looks in the right direction. Let me try it out!

• ###### Re: Expression for count if the average is greater than target number

FYI, You'll need a group by when you take the average...

WORK_TEAM_ID

, avg(DAYS_TO_COMPLETE) as [Avg Days To Complete]

Resident FactTable

Group by WORK_TEAM_ID.

Rookie mistake by me leaving that out....

• ###### Re: Expression for count if the average is greater than target number

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

• ###### Re: Expression for count if the average is greater than target number

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....

• ###### Re: Expression for count if the average is greater than target number

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?

• ###### Re: Expression for count if the average is greater than target number

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.

• ###### Re: Expression for count if the average is greater than target number

Thanks. I will have to keep looking then.