Can I create a pivot table showing only top 3 employees with their previous year rank?

[james227]

Hi is it possible to get a pivot table that only shows top 3 employees for current year but their rank from the previous year compared to the entire set of employees?

Example entire data set:

Name	Current Rank	Previous year rank
John	1	n/a
Joe	2	1
Jim	3	4
Jack	4	n/a
Jill	5	2

What I want to get is:

Name	Current Rank	Previous year rank
John	1	n/a
Joe	2	1
Jim	3	4

What I am currently getting is :

Name	Current Rank	Previous year rank
John	1	3
Joe	2	1
Jim	3	2

I am using an AGGR(IF(rank(sum({$<Year = {'$(=vMaxYear)'}>}Sales)))<=3,Name),Name)  as my dimension.

And

=RANK(sum({$<Year = {'$(=vMaxYear)'}>}Sales))

=RANK(sum({$<Year = {'$(=vPriorYear)'}>}Sales))  as my expressions.

Can anyone help? @swuehl @Lisa_P 

[Saravanan_Desingh]

I am assuming you have two tables like this.

tab1:
LOAD * INLINE [
    Name, Current Rank
    John, 1
    Joe, 2
    Jim, 3
    Jack, 4
    Jill, 5
];

Left Join(tab1)

tab2:
LOAD * INLINE [
    Name, Previous year rank
    John, n/a
    Joe, 1
    Jim, 4
    Jack, n/a
    Jill, 2
];

[Saravanan_Desingh]

Use Dimension Limit to Select the First 3.

If you have different Data Model, please let me know.

[james227]

My data model is Employees, #sales, date.  So the ranks are not stored in the model and I'm using qlikview's rank function to derive them.  That's where I seem to have the problem because the ranks are relative.

[Saravanan_Desingh]

Can you share a sample data with Sales value and Date?

[james227]

Sure,

LOAD * INLINE [
    Name, Sales, Year
    John, 100, 2020
    Joe, 80, 2020
    Jim, 60, 2020
    Jack, 40, 2020
    Jill, 20, 2020
    Joe, 100, 2019
    Jim, 20, 2019
    Julia, 60, 2019
    Jill, 80, 2019

];

[Saravanan_Desingh]

The below Script will help to create the Table with Ranks. Along with the Chart you can show the output.

tab1:
LOAD *, AutoNumber(RowNo(),Year) As Rank;
LOAD * INLINE [
    Name, Sales, Year
    John, 100, 2020
    Joe, 80, 2020
    Jim, 60, 2020
    Jack, 40, 2020
    Jill, 20, 2020
    Joe, 100, 2019
    Jim, 20, 2019
    Julia, 60, 2019
    Jill, 80, 2019
];

tab2:
LOAD Name, Rank As [Current Rank] 
Resident tab1
Where Year=2020;

Left Join(tab2)
LOAD Name, Rank As [Previous year rank] 
Resident tab1
Where Year=2019;

Drop Table tab1;

[NitinK7]

Hi 

you can try below 

dimension-   Aggr(Only({<Year={'$(=Max(Year))'}, Sales={"=Rank(Aggr(Sum(Sales),Sales))<=3"}>}Name),Name)

expression -  Rank(Aggr(Sum(Sales),Year,Sales),4,1).

Thanks,

Nitin.