topic Re: Can I create a pivot table showing only top 3 employees with their previous year rank? in App Development

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

james227 — Sat, 16 Nov 2024 02:06:54 GMT

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

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

Saravanan_Desingh — Fri, 19 Jun 2020 03:26:04 GMT

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 ];

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

Saravanan_Desingh — Fri, 19 Jun 2020 03:28:42 GMT

Use Dimension Limit to Select the First 3.

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

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

james227 — Fri, 19 Jun 2020 11:56:24 GMT

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.

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

Saravanan_Desingh — Fri, 19 Jun 2020 15:37:52 GMT

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

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

james227 — Mon, 22 Jun 2020 12:54:41 GMT

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

];

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

Saravanan_Desingh — Mon, 22 Jun 2020 13:38:11 GMT

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;

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

NitinK7 — Mon, 22 Jun 2020 14:38:53 GMT

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.