Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
Use Dimension Limit to Select the First 3.
If you have different Data Model, please let me know.
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.
Can you share a sample data with Sales value and Date?
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
];
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;
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.