That's the first thing I tried but I get blanks for all values. I am wondering if it's because of the Incentive column and how or when it's calculated.
With the below formula populating the Incentive column, would the values exist at the run time of the formula Aggr(Min(Incentive), %ADAcctKey)
It's working but doing something very wrong.
So I have changed my model and it is now allowing me to calculate using the above formula but it's giving an odd result.
I am using the formula Aggr(Min(Achieved), %ADAcctKey) To Identify the Achieved Level by the agent.
Notice the Minimum Score to the right which shows that value of 0 as the lowest achieved and this is correct. It is just on the incorrect line. When I create a second chart to provide a list of agents lowest achieved level with only that row I now get incorrect rows.
%ADAcctKey Category IncentiveLevel Achieved Score Minimum Score 37 Procuctivity Stretch 0.2 0.888 37 QA Target 0.15 91.74 0 37 Revenue Threshold 0.05 1223.98 37 Adherence Below 0 0.8454
The consistent part is that the value is placed on the first portion of our load script as seen below. Notice how the first Metric loaded below in the Resident load is QA. All the lowest values in the chart are displaying there. When I moved QA to the bottom of the script, all scores then showed in the row for Adherence, the new first loaded.
/***[ Initial load where each agents score for all categories is in a single row]***/
[Customer Care Incentive Extract.xls]
(biff, embedded labels, table is Sheet1$);
/***[ Each metric is then split out into a single column and later joined to the KPI range]***/
RoleID & 2 as VariableKey,
QA as IncentiveScore,
'QA' as IncentiveCategory
RoleID & 2 as VariableKey,
Adherence as IncentiveScore,
'Adherence' as IncentiveCategory
Thank you for the help.
The formula Aggr( nodistinct Min(Achieved), %ADAcctKey) places the lowest value in all rows for the agent which will work but does not allow us to only display the single row.
With the formula min(total <%ADAcctKey > Min(Achieved)) I get all null values. I am curious as to the need for the '<>' around <%ADAcctKey >.
You probably have some code after the script snippet you've posted above, but just to be sure:
Are you dropping table load_IncentiveScores after transforming the columns to rows? If not, you will create a synthetic table that will link the columns with same name. In your scenario, I believe that's not what you want.
Do you have a synthetic table in your data model?
Again, if you want us to help you, you need to post more detailed information about your data model, best by creating a small sample QVW that demonstrates this issue.
App showing the Aggr() value on the wrong row.
I also attempted this with Rank() during my process but that is additional work on the load script.
Look at the table 'Using Aggr() - Minimum Score' with EmpID 31480 if it's not selected. You will see using Aggr( Min(AchievedIncentive), EmpID) places the lowest score in 15% when it's really 10%. This works correctly on the majority of the values.
Thank you everyone for your help. I have been able to create what I needed and part of the problem was how the app that feeds these numbers was built. I have uploaded an app that takes the raw scores and builds what I need. I will work further to have the back in the source app but this is working for now.