topic Re: Dense Rank in QlikView

Dense Rank

kkrause — Mon, 22 Aug 2011 13:50:37 GMT

Hi,

This might be very easy but I could not figure it out. I'm looking for a function for a dense rank in QlikView.

For example:

dense_rank( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )

How does it work in QlikView pivot or table?

The function will be needed on pivot or table level.

The ranking can't be precalculated during load (skript) because one of the criteria will be calculated on the fly (tariff simulator).

Many thanks in advance.

Dense Rank

swuehl — Mon, 22 Aug 2011 14:37:00 GMT

Kai,

start with building your pivot or straight table, choose the dimensions as needed and set an expression after which you want the table's rows ranked.

Then you need a rank function, e.g. if you have sum(Sales) as expression and SalesPerson as dimension,

your rank could look like

= rank(sum(Sales))

This should already give a ranking of SalesPerson after Sales Amount)

As I understood a dense rank is special, because it assigns equal values the same rank number, but continues ranking with the next rank order number, e.g. you may get something like:

Sales, Rank

300, 1

200, 2

100, 3

In QV, you set the rank mode as second parameter to rank function, but I think density rank is no standard mode in QV, so if this is really needed, we have to look for a work around.

But as a start off, try building a table and use rank function to get used to the QV charts and ranking (and have a look into rank() function in help file).

Regards,

Stefan

Re: Dense Rank

kkrause — Wed, 31 Aug 2011 14:49:33 GMT

Thanks for the first hint.

For my solution it also helpful to rank over an partition CUSTOMER_ID (see example).

The column "value" will be calculated dynamically in a pivot table in dependency to a tariff tableau.

How must the statement looks like to generate this ranking?

Thanks.

Re: Dense Rank

swuehl — Wed, 31 Aug 2011 14:59:07 GMT

Kai,

please have a look at attached. Ranking will be per column segment, so if you want to rank customer's values per customer, you need a second dimension, I used Value for this, but probably something unique like OrderID etc. would be better.

And just replace Value with your expression needed to calculate the Value.

Regards,

Stefan

Re: Dense Rank

Wed, 12 Mar 2014 13:06:59 GMT

Nine one, thanks Stefan.