Rank based on 2 expressions

[olivetwist]

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?

[sunny_talwar]

May be like this:

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

[olivetwist]

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.

[sunny_talwar]

Yes, sample would be good to look at

[NickHoff]

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.