topic Re: Re: rank in pivot table in QlikView

rank in pivot table

Mon, 09 Dec 2013 12:59:45 GMT

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?

Re: rank in pivot table

jolivares — Mon, 09 Dec 2013 13:25:34 GMT

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.

Re: rank in pivot table

Mon, 09 Dec 2013 14:22:12 GMT

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.

Re: Re: rank in pivot table

jolivares — Mon, 09 Dec 2013 14:30:32 GMT

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

Re: Re: Re: rank in pivot table

jolivares — Mon, 09 Dec 2013 15:20:55 GMT

Check again...

Re: rank in pivot table

jerem1234 — Mon, 09 Dec 2013 15:50:07 GMT

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!

Re: rank in pivot table

Mon, 09 Dec 2013 15:57:11 GMT

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

Re: Re: rank in pivot table

Tue, 10 Dec 2013 11:52:50 GMT

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)

Re: Re: Re: rank in pivot table

jerem1234 — Tue, 10 Dec 2013 15:57:30 GMT

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.