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:
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?
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.
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):
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)
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.