Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
coolwaters
Contributor II
Contributor II

Rank Difference Across Years

Hi,

I have sales data of customers across years. I need to get the rank of the customer by the year and then get the difference in the rank between two years. ie. If the customer has moved up or down etc.

I am trying to achieve the output as per the below.

Rank Report.png

Have attached the sample data.

Note: Am using QV 11.20 personal edition thus please post the script and the expressions for the solution as will not be able to access other QVW's.

Thanks.

1 Solution

Accepted Solutions
sunny_talwar

Here are the new expressions

=Sum({<Year = {$(vYearFrom)}>}Sales)

=If(Column(1) <> 0 or Column(3) <> 0, If(Sum({<Year = {$(vYearFrom)}, Customer>}Sales) > 0, Rank(Sum({<Year = {$(vYearFrom)}, Customer>}Sales), 3), 'NOT IN ' & $(vYearFrom)))

=Sum({<Year = {$(vYearTo)}>}Sales)

=If(Column(1) <> 0 or Column(3) <> 0, If(Sum({<Year = {$(vYearTo)}, Customer>}Sales) > 0, Rank(Sum({<Year = {$(vYearTo)}, Customer>}Sales), 3), 'NOT IN ' & $(vYearTo)))

=If(Column(1) <> 0 or Column(3) <> 0, If(SecondaryDimensionality() > 0,

If(Sum({<Customer>}Sales)=0,'NOT IN YEAR',Rank(Sum({<Customer>}Sales))),

If(Sum({<Year = {$(vYearFrom)}, Customer>} Sales)=0,'NOT IN '&$(vYearFrom),

If(Sum({<Year = {$(vYearTo)}, Customer>} Sales)=0,'NOT IN '&$(vYearTo),

Rank(Sum({<Year={$(vYearFrom)}, Customer>} Sales),0,1)-Rank(Sum({<Year = {$(vYearTo)}, Customer>} Sales),0,1)))))

View solution in original post

17 Replies
sunny_talwar

Script

Table:

LOAD Customer,

    Year,

    Sales

FROM

[..\..\Downloads\Rank.xlsx]

(ooxml, embedded labels, table is Sheet1);

Straight Table

Dimension

Customer

Expressions

=Sum({<Year = {$(=Min(Year))}>}Sales)

=If(Sum({<Year = {$(=Min(Year))}>}Sales) > 0, Rank(Sum({<Year = {$(=Min(Year))}>}Sales), 3))

=Sum({<Year = {$(=Max(Year))}>}Sales)

=If(Sum({<Year = {$(=Max(Year))}>}Sales) > 0, Rank(Sum({<Year = {$(=Max(Year))}>}Sales), 3))

=Column(2) - Column(4)

Capture.PNG

Note: Attaching the qvw for other visitiors

effinty2112
Master
Master

Hi Cool,

=rank(Sum(Sales),1,1)

Gives this pivot table:

Customer Year 2014201520162017
Customer A 2121
Customer B 2324
Customer C 4214
Customer D 1323
Customer E 4322

OR

Rank(sum({$<Year={$(=-3+max(Year))}>}Sales),1,1)

& '=>' &

Rank(sum({$<Year={$(=-2+max(Year))}>}Sales),1,1)

& '=>' &

Rank(sum({$<Year={$(=-1+max(Year))}>}Sales),1,1)

& '=>' &

Rank(sum({$<Year={$(=max(Year))}>}Sales),1,1)

gives this straight table:

Customer Y to Y
Customer A2=>1=>2=>1
Customer B2=>3=>2=>4
Customer C4=>2=>1=>4
Customer D1=>3=>2=>3
Customer E4=>3=>2=>2

Cheers

Andrew

coolwaters
Contributor II
Contributor II
Author

Hi Sunny,

Thanks for your reply. I also need to mention 'NOT IN' followed by the YEAR in the chart as per the image attached.

Can you help me get that too.

Thanks.

coolwaters
Contributor II
Contributor II
Author

Hi Andrew,

Thanks for your reply. I also need to mention 'NOT IN' followed by the YEAR in the chart as per the image attached.

Can you help me get that too.

Also for eg. Customer B has sales only in 2014, thus for what i am trying to do there should be no rank for the customer in the years where there is no sales.

Thanks.

antoniotiman
Master III
Master III

Hi,

Expressions :

Sum(Sales)

If(SecondaryDimensionality() > 0,
If(Sum(Sales)=0,'NOT IN YEAR',Rank(Sum(Sales))),
If(Sum({<Year={'$(=Min(Year))'}>} Sales)=0,'NOT IN '&Min(Year),
If(Sum({<Year={'$(=Max(Year))'}>} Sales)=0,'NOT IN '&Max(Year),
Rank(Sum({<Year={'$(=Min(Year))'}>} Sales),0,1)-Rank(Sum({<Year={'$(=Max(Year))'}>} Sales
),0,1))))

Regards,

Antonio

effinty2112
Master
Master

Hi Cool,

How about:

Year 20142017
Customer SalesRankRank changeSalesRankRank change
Customer A20002 28001+1
Customer B20002 0Not in 2017-2
Customer D25001 20003-2
Customer E0Not in 2014 25002+2

Rank: if(Sum(Sales) >0,rank(Sum(Sales),1,1), 'Not in ' & Year)

Rank change: Before(rank(Sum(Sales),1,1)) - rank(Sum(Sales),1,1)

Format the number return by Rank change to get +/-

good luck

Andrew

sunny_talwar

Expressions

=Sum({<Year = {$(=Min(Year))}>}Sales)

=If(Sum({<Year = {$(=Min(Year))}>}Sales) > 0, Rank(Sum({<Year = {$(=Min(Year))}>}Sales), 3), 'NOT IN ' & Min(TOTAL Year))

=Sum({<Year = {$(=Max(Year))}>}Sales)

=If(Sum({<Year = {$(=Max(Year))}>}Sales) > 0, Rank(Sum({<Year = {$(=Max(Year))}>}Sales), 3), 'NOT IN ' & Max(TOTAL Year))

=If(Sum({<Year = {$(=Min(Year))}>}Sales) = 0 and Sum({<Year = {$(=Max(Year))}>}Sales) = 0, 'NOT IN ' & Min(TOTAL Year) & ' and ' & Max(TOTAL Year),

If(Sum({<Year = {$(=Min(Year))}>}Sales) = 0, 'NOT IN ' & Min(TOTAL Year),

If(Sum({<Year = {$(=Max(Year))}>}Sales) = 0, 'NOT IN ' & Max(TOTAL Year), Column(2) - Column(4))))

Capture.PNG

coolwaters
Contributor II
Contributor II
Author

Hi Antonio,

Thank you for taking the time out and for the solution.

Just one thing. If i pass the two years as variables and not as selections in the list box then the problem i am facing is that Customer B that has no sales in either of the two years that is 2014 & 2017 still shows up in the chart.

There are a lot of customers like this thus i end up having the customer name and 0 as sales for them for both the years.

Also if i select any customer in the list box the rank for the customer changes how can i include AGGR or Total  or another solution in the expression to avoid this.

Thanks again.

antoniotiman
Master III
Master III

1) Why the behavior sould change ?

2) I think You must achieve this in Script (like TableOfAs)

Regards,

Antonio