Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

darrin_pilkingt
Contributor II

Trying to find the lowest value per Dimension, possibly aggr()?

Hello,

I have a chart and I need to identify the lower Incentive value for each %ADAcctKey.

The Incentive value is a calculated value.  In the example below 445 would flag 10% and for 2544 the lowest flagged would be 0%.

    

%ADAcctKeyMetricDescQAAdherenceRevenueSavesLevelTargetIncentive
445QA87.50% Target80% >=10%
445Adherence 88.04% Target88% >=10%
2544Save Rate 26.09%Stretch22% >=20%
2544QA81.68% Target80% >=15%
2544Adherence 74.34% Below85% <0%
2544Revenue $335.40 Below$750 <0%

Thank you for you help.

1 Solution

Accepted Solutions

Re: Trying to find the lowest value per Dimension, possibly aggr()?

Use the following expression:

Aggr(Min(Incentive), %ADAcctKey)

11 Replies

Re: Trying to find the lowest value per Dimension, possibly aggr()?

Use the following expression:

Aggr(Min(Incentive), %ADAcctKey)

darrin_pilkingt
Contributor II

Re: Trying to find the lowest value per Dimension, possibly aggr()?

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)

Incentive =

if(QA=0,0,

if(Productivity=0,0,

if(Adherence=0,0,

if([Revenue]=0,0,

if(Saves=0,0,

AchievedIncentive)))))

MVP
MVP

Re: Trying to find the lowest value per Dimension, possibly aggr()?

Maybe like this

=Min(Total<%ADAcctKey>

Aggr(

if(QA=0,0,

if(Productivity=0,0,

if(Adherence=0,0,

if([Revenue]=0,0,

if(Saves=0,0,

AchievedIncentive)))))

, %ADAcctKey, MetricDesc)

)

darrin_pilkingt
Contributor II

Re: Trying to find the lowest value per Dimension, possibly aggr()?

Swuehl thanks,

Still getting blanks.  I think I may need to do some remodeling to simplify some things.

MVP
MVP

Re: Trying to find the lowest value per Dimension, possibly aggr()?

Ok, could you describe how your data model is built, and which dimensions and expressions you're using in the chart.

It would be very helpful if you could upload a small sample QVW that demonstrates your data and chart.

darrin_pilkingt
Contributor II

Re: Trying to find the lowest value per Dimension, possibly aggr()?

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.

%ADAcctKeyCategoryIncentiveLevelAchievedScoreMinimum Score
37ProcuctivityStretch0.20.888
37QATarget0.1591.74                0
37RevenueThreshold0.051223.98
37AdherenceBelow00.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]***/

load_IncentiveScores:

LOAD %ADAcctKey,

    AgentName,

    RoleShortDesc,

    RoleID,

    Procuctivity,

    Saves,

    QA,

    Adherence,

    Revenue

FROM

[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]***/

tmpIncentiveScores:

LOAD %ADAcctKey,

  RoleID & 2 as VariableKey,

    AgentName,

    RoleShortDesc,

    RoleID,

    QA as IncentiveScore,

    'QA' as IncentiveCategory

Resident load_IncentiveScores;

Concatenate(tmpIncentiveScores)

LOAD %ADAcctKey,

  RoleID & 2 as VariableKey,

    AgentName,

    RoleShortDesc,

    RoleID,

    Adherence as IncentiveScore,

    'Adherence' as IncentiveCategory

Resident load_IncentiveScores;

ramoncova06
Valued Contributor III

Re: Trying to find the lowest value per Dimension, possibly aggr()?

have you tried using no distinct?

Aggr( nodistinct Min(Achieved), %ADAcctKey)

also why not use min(total <%ADAcctKey > Min(Achieved)) ? this tends to give you the same results that by using aggr but has better performance

darrin_pilkingt
Contributor II

Re: Trying to find the lowest value per Dimension, possibly aggr()?

Ramon,

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

Thanks again.

ramoncova06
Valued Contributor III

Re: Trying to find the lowest value per Dimension, possibly aggr()?

sorry it should be min(total <%ADAcctKey > (Achieved))

the <> after a total does a grouping by that field

Community Browser