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: 
Not applicable

rank in pivot table

Hello all,

I am trying to create a pivot table, showing the sales per country, customer id and customer name.

In addition to the sales column, I want to have a column to show the rank of the individual sales.

The outcome should look like following at the end:

CountryCustomer IDCustomer NameSalesRank
FranceCustomer ID1Customer 11002
FranceCustomer ID2Customer 2503
FranceCustomer ID3Customer 32001
GermanyCustomer ID4Customer 44001
GermanyCustomer ID5Customer 51002

Country, Customer ID and Customer Name are dimensions, Sales and Rank are expressions.

I am using an expression with the Rank function, which looks like following :HRank(SUM(Sales)).

However, it returns the rank for each individual line. So at the end, it shows for each line rank "1" as (for some reason), it is not comparing the sales of that single combination Country+CustomerID+Customername vs the total country sales.

I tried several different syntax but none of them worked.

Does anyone have an idea, what needs to be changed here?

8 Replies
jolivares
Specialist
Specialist

as I see in your example QV is ranking your data by Country, but you want that your rank is overall the sales value.

Try this expression:  Rank(Total Sum(Sales)).

Int this case the TOTAL don't take care of what dimensions you have selected.

Not applicable
Author

Hi Juan,

I think you misunderstood this.

The table above shows the required outcome.

But at the moment, the ranking function returns "1" for every line. It is basically ranking the data by each customer, not by country.

I already tried the expression by using the Total modifier, but still no change.

jolivares
Specialist
Specialist

Ok... Check the example attached.  Note that I use different name for the column but at the end is similar.

jolivares
Specialist
Specialist

Check again...

jerem1234
Specialist II
Specialist II

For the rank function, it depends on what dimensions you have. Look at this text from Qlikview Help on the rank function:

"If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column. If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order."

In your example, it is using the first two dimensions. This way every record is grouped with itself, so they all get a rank of 1. Remove Customer Name as dimension and put as expression, and if Sales is a dimension, change it to be sum(Sales). Hopefully this will resolve your issue.

Hope this helps!

Not applicable
Author

Ross,

Coudl you try this:

 

rank(aggr(sum(Sales), Country, CustomerID))

AGGR creates a "table" dimensionned by Country and CustomerID. The rank returns the numer according to the rank it gets.

Fabrice

Not applicable
Author

Hi jerem1234 and Fabrice,

jerem, you are correct. The rank function basically returns the ranking for each individual customer. So every customer gets the rank 1. Your hint with removing one column from the dimensions and adding it to the expression works indeed. I moved the customer ID column from the dimensions and added it to the expressions.

However, there is still an issue with that solution.

I added another dimension (month dimension), to show the sales+rankings per month. The column was added on the x-axis.

But it then repreats the column customer id for every month (see picture below):

QV TEst.jpg

Is there a way to create the ranking while using country, customer name, customer id and month as dimensions?

I tried several functions, including the one from Fabrice (using Aggr) but it doesnt work out yet (see in the attached file)

jerem1234
Specialist II
Specialist II

I've done it in this way:

Instead of having Customer ID as an expression or dimension, I just used one dimension of Customer Name & Customer ID concatenated since there is a 1-1 mapping (I'm assuming). Then the rank function can be used.

Please find attached. Hope this works for you.