topic Re: Rank Difference Across Years in QlikView

Rank Difference Across Years

coolwaters — Wed, 25 Nov 2020 16:16:04 GMT

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.

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.

Re: Rank Difference Across Years

sunny_talwar — Wed, 12 Jul 2017 15:28:35 GMT

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)

Note: Attaching the qvw for other visitiors

Re: Rank Difference Across Years

effinty2112 — Wed, 12 Jul 2017 15:38:01 GMT

Hi Cool,

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

Gives this pivot table:

Customer	Year	2014	2015	2016	2017
Customer A		2	1	2	1
Customer B		2	3	2	4
Customer C		4	2	1	4
Customer D		1	3	2	3
Customer E		4	3	2	2

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 A	2=>1=>2=>1
Customer B	2=>3=>2=>4
Customer C	4=>2=>1=>4
Customer D	1=>3=>2=>3
Customer E	4=>3=>2=>2

Cheers

Andrew

Re: Rank Difference Across Years

coolwaters — Wed, 12 Jul 2017 15:42:09 GMT

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.

Re: Rank Difference Across Years

coolwaters — Wed, 12 Jul 2017 15:42:47 GMT

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.

Re: Rank Difference Across Years

antoniotiman — Wed, 12 Jul 2017 15:52:42 GMT

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

Re: Rank Difference Across Years

effinty2112 — Wed, 12 Jul 2017 16:00:38 GMT

Hi Cool,

How about:

Year	2014			2017
Customer	Sales	Rank	Rank change	Sales	Rank	Rank change
Customer A	2000	2		2800	1	+1
Customer B	2000	2		0	Not in 2017	-2
Customer D	2500	1		2000	3	-2
Customer E	0	Not in 2014		2500	2	+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

Re: Rank Difference Across Years

sunny_talwar — Wed, 12 Jul 2017 16:06:36 GMT

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))))

Re: Rank Difference Across Years

coolwaters — Wed, 12 Jul 2017 16:08:46 GMT

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.

Re: Rank Difference Across Years

antoniotiman — Wed, 12 Jul 2017 16:39:27 GMT

1) Why the behavior sould change ?

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

Regards,

Antonio

Re: Rank Difference Across Years

coolwaters — Wed, 12 Jul 2017 20:33:24 GMT

Thanks for the solutions.

Please find attached the qvw of what i am doing, have taken inputs from Sunny's solution as am trying to work with a straight table !. The issue i am facing is Customer C should not be in the chart at all as there is no sales for this customer in either of the years the analysis is being done on. And there as variables being used for assigning the years.

Please let me know where i am going wrong.

Thanks !

Re: Rank Difference Across Years

Anonymous — Wed, 12 Jul 2017 20:41:52 GMT

I am CERTAIN there are other ways.

But here is 1 way based on what you provided.

It uses dimension limits and a hidden expression that must be the first expression.

Re: Rank Difference Across Years

sunny_talwar — Wed, 12 Jul 2017 22:30:16 GMT

Did not look at Wallo's solution, but here might be another way...

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

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

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

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

=If(Column(1) <> 0 or Column(3) <> 0, 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)))))

Re: Rank Difference Across Years

coolwaters — Wed, 12 Jul 2017 23:00:46 GMT

Hi Wallo,

Thank you. Unfortunately i am using QV Personal Edition and thus am unable to access the QVW posted by you.

If you can post the script / expressions then that would be helpful.

Thanks again.

Re: Rank Difference Across Years

coolwaters — Wed, 12 Jul 2017 23:02:41 GMT

Hi Sunny,

It works, just one thing. When a customer is selected then the rank changes based on the selection. Thus can we include Aggr or something else to stop that from happening.

Thanks again.

Re: Rank Difference Across Years

sunny_talwar — Wed, 12 Jul 2017 23:11:01 GMT

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)))))

Re: Rank Difference Across Years

coolwaters — Wed, 12 Jul 2017 23:18:40 GMT

Hi Sunny,

Thanks a ton.

Works well.

For my understanding please explain the use of 3 in the expression.

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

Thank you again.

Re: Rank Difference Across Years

coolwaters — Wed, 12 Jul 2017 23:19:33 GMT

Thank you all for taking the time out to help.

Appreciate it, always.