Set Analysis

[Anonymous]

Hi all,

I have a requirement

I have two fields Manager, Employee

Manager          Employee

     a                     123

     a                     234

     b                     234

     c                     343

     a                     543

     b                     123

     a                     555

     a                     723    

Here i want to display the Manager in text object who is having more employees under him compared to other managers.

How do i achieve this.?

[Kushal_Chawda]

= FirstSortedValue(distinct Manager, -aggr(count(distinct Employees),Manager))

[effinty2112]

Hi Kishore,

Try

=Concat(Aggr(if(Rank(Count(Employee),1,1) = 1,Manager),Manager),',')

If there is a tie for first place the expression will give the managers seperated by  a comma.

Regards

Andrew

[Anonymous]

adding up to this,

Manager      week1       week2       Employee    

     a                 10              10              123              

     a                 10              20              234             

     b                 20              10              234

     c                  3                2               343

     a                  5                6               543

     b                  2                2               123

     a                  2                3               555

     a                  1                4               723   

Here i want to display the Manager along with his week1 and week2

[Anonymous]

adding up to this,

Manager      week1       week2       Employee   

     a                 10              10              123             

     a                 10              20              234            

     b                 20              10              234

     c                  3                2               343

     a                  5                6               543

     b                  2                2               123

     a                  2                3               555

     a                  1                4               723  

Here i want to display the Manager along with his week1 and week2

[effinty2112]

This?

Manager	sum(week1)	sum(week2)
a	28	43

Manager is the calculated dimension:

=Aggr(if(Rank(Count(Employee),1,1) = 1,Manager),Manager)

Regards

Andrew

[MarcoWedel]

Hi,

another solution might be a straight table with dimension limits:

hope this helps

regards

Marco

[Kushal_Chawda · Accepted solution]

try below expression for week

=Concat( DISTINCT{<Manager ={"$(=FirstSortedValue(distinct Manager, -aggr(count(distinct Employee),Manager)))"}>}week1,',',week1)

=Concat( DISTINCT{<Manager ={"$(=FirstSortedValue(distinct Manager, -aggr(count(distinct Employee),Manager)))"}>}week2,',',week2)

[MarcoWedel]

Glad you liked it.

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco