Rank Difference Across Years

[coolwaters]

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.

[coolwaters]

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 !

[Anonymous]

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.

[sunny_talwar]

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

[coolwaters]

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.

[coolwaters]

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.

[sunny_talwar · Accepted solution]

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

[coolwaters]

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.

[coolwaters]

Thank you all for taking the time out to help.

Appreciate it, always.