Max of trades help

[mahitham]

Hi Experts,

Can any one please help me on below requirement.

I have created a below Pivot Table in Qlik.The Measure will show the current month sales.

The requirement is  I have to show only Department wise Max Trades employee name and Trade count only instead of all the  other employees like expected Table.

Note Here Trade field is the ID field.

Current Table:

Expected Table:

Please help me on this.

Thanks in advance

[mahitham]

HI @sunny_talwar 

Could you please help me on above requirement.

Thanks in advance.

[tresesco]

Try like:

Dimension: Department
Exp1 for Employee:

 FirstSortedValue(Employee, -Aggr(Count({<MonthDiff={0}>} Trades),Department, Employee))

Exp2:

 Max(Aggr(Count({<MonthDiff={0}>} Trades),Department, Employee))

[mahitham]

Hi @tresesco 

Thanks for your help.

The expressions are working fine but has some issue here could you please help me on this.

In each department there are some null employees . Those Null employees related Trade count is high but here need to show only not  null employees Highest  trade count for example by using above expressions its considering Electronics Department --> Highest Trade count as 1500 but its null count. Need to show Suraj count as 450.

Note: This Null Count is in only under few departments not in all the departments.

Please help me on this.

Thanks in advance.

[Shubham_Deshmukh]

Use this in your expression : 

=AGGR(Max(yourExpression),Department)

[Shubham_Deshmukh]

I got result with this,

MaxSale:
LOAD * INLINE [
xx1, xx2, xx3
entc, A, 10
entc, A, 11
entc, A, 12
entc, B, 20
entc, B, 21
entc, C, 30
comp, X, 51
comp, X, 52
comp, Y, 30
Mech, P, 100
Mech, P, 101
Mech, Q, 200

];

NoConcatenate
Load xx1,xx2, count(xx3) as x3
Resident MaxSale

group by xx1,xx2;

Use below expression in chart: 

=aggr(max(x3),xx1)

[mahitham]

Hi @Shubham_Deshmukh 

I have tried like below but not getting any count.

=AGGR(Max(Count({<MonthDiff={0}>} Trades),Department)

By using below expression mentioned by @tresesco  getting highest  Trade count  but its the null employees count need to show the max count from not null employees count

Max(Aggr(Count({<MonthDiff={0}>} Trades),Department, Employee))

[Shubham_Deshmukh]

=AGGR(Max(Count({<MonthDiff={0}>} Trades),Department) will give error,

Try another solution which I mentioned, I got results.

[mahitham]

Hi @Shubham_Deshmukh 

But getting synthetic keys in the datamodel. 

I have to apply this logics in Department -Manger ,Department Employee,Department-Ass Manager also in that case if I create 3 group by tables it will show many synthetic keys.

please help me to avoid this null row .

Is there any way to resolve this.

Thanks in advance.

[tresesco · Accepted solution]

You could use set analysis to exclude such employees like:

FirstSortedValue( {<Employee={"=len(trim(Employee))>0"}>}Employee, 
-Aggr(Count({<Employee={"=len(trim(Employee))>0"},MonthDiff={0}>} Trades),Department, Employee))

Max({<Employee={"=len(trim(Employee))>0"}>} 
Aggr(Count({<Employee={"=len(trim(Employee))>0"},MonthDiff={0}>} Trades),Department, Employee))