Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Country | Customer ID | Customer Name | Sales | Rank |
---|---|---|---|---|
France | Customer ID1 | Customer 1 | 100 | 2 |
France | Customer ID2 | Customer 2 | 50 | 3 |
France | Customer ID3 | Customer 3 | 200 | 1 |
Germany | Customer ID4 | Customer 4 | 400 | 1 |
Germany | Customer ID5 | Customer 5 | 100 | 2 |
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?
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.
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.
Ok... Check the example attached. Note that I use different name for the column but at the end is similar.
Check again...
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!
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
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):
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)
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.