topic Re: Rank based on 2 expressions in QlikView

Rank based on 2 expressions

olivetwist — Thu, 01 Sep 2016 15:26:55 GMT

We have an application that is used for reporting on a productivity database.

The expressions we are ranking off of are "number of transactions" and "Avg time per transaction".

If the individual had more than 3 transactions, rank by "number of transactions" else rank by "Avg time per transaction".

We have both ranks working in separate columns:

User # trans Avg time Rank1 Rank2

bob 3 1:00 1

lisa 7 1:20 2

chris 15 1:45 1

john 4 0:30 3

What we want to see:

User # trans Avg time Rank

bob 3 1:00 4

lisa 7 1:20 2

chris 15 1:45 1

john 4 0:30 3

How can this be accomplished?

Re: Rank based on 2 expressions

sunny_talwar — Thu, 01 Sep 2016 15:41:45 GMT

May be like this:

=Rank(RangeSum(If([# trans] > 3, [# trans], 0), [Avg time]))

Re: Rank based on 2 expressions

olivetwist — Thu, 01 Sep 2016 17:29:43 GMT

This works great with a straight table. I am also trying to accomplish this in a Chart ranking based on 2 expressions. Unable to Ref the column name and when we substitute the expressions directly, it is basically ranking by the Time only. i will try to get an example qvw uploaded.

Re: Rank based on 2 expressions

sunny_talwar — Thu, 01 Sep 2016 17:52:45 GMT

Yes, sample would be good to look at

Re: Rank based on 2 expressions

NickHoff — Thu, 01 Sep 2016 18:07:32 GMT

Oliver,

You can accomplish this by parameterizing your variables. LIke the following:

Keep in mine $(vTopPhysicians) is just the value of the limit you'd like to rank.

$(vRank) is just a variable that = RankDim

=COUNT({<ProcedureSurgeonName = {"=RANK($(vRank),4)<=$(vTopPhysicians)"}>}DISTINCT SurgicalCaseSk)

Then you'll create an inline table with the expression you'd like to rank similar to:

MetricExpression:

LOAD * INLINE [

RankDim, Expression, @FormatString

Volume, COUNT(DISTINCT SurgicalCaseSk), '#,##0'

SMART Supply Cost,SUM(SupplyChainLUOMTotalCost), '$#,##0.00'

SMART Supply Cost Per Case,SUM(SupplyChainLUOMTotalCost)/COUNT(DISTINCT SurgicalCaseSk), '$#,##0.00'

];

Now in your app to switch between what you are ranking you'll use RankDim as a listbox.