topic Re: Need help with ranking in App Development

Need help with ranking

Fri, 06 Jan 2017 22:50:03 GMT

Dears,

Could you please help me with rank functions.

I have the following:

Biz	Deal	Value
A	Red	10
B	Blue	15
C	Small	5
A	Flow	8
C	Small	6

So, I'd like to rank in separate column by Biz, By Deal, By Value.

Thank you in advance.

Re: Need help with ranking

petter — Sat, 07 Jan 2017 00:47:05 GMT

I am not sure if this is what you had in mind?

I have associated both Biz and Deal with a numeric value so they can be ranked. Maybe you just want plain sorting and creating dual-values with the Dual()-function will enable various qualitative text strings to be sorted according to a ranking value.

For instance:

QUALITY

Bad 0

Neutral 1

Good 2

Better 3

Best 4

Using a dual-function Dual( <text> , <value) will make this association and whenever the field values are sorted it will prefer to use the numeric part to determine sort order. If you don't have a dual-value but pure text it will be sorted by "natural sort order" (almost like alphabetical sort except for multi digit numbers).

Re: Need help with ranking

Sat, 07 Jan 2017 10:17:27 GMT

Petter, thank you for you answer.

I am not sure that this is exactly what I look for.

I found the key if I want to rank each possitions (as in the example below).

I created new dimension w/ the formula: aggr (rank([Actual]),[Actual]), but could not find the way how to rank by biz and deal (oppt in my below example).

In other words I want to have two more columns: where A has rank 1, B-2, C-3 and the second one where Oppt Calik has rank 1, TPO - 2, Small - 3 and Flow - 4.

The problem I faced is that in 'Small' I always have '-'.

Thanks.

Alexander

Re: Need help with ranking

MK_QSL — Sat, 07 Jan 2017 10:26:25 GMT

Create a Straight table

Dimension

Biz

Deal

Value

Expression

Num(Rank(Total SUM(Value),4))

Re: Need help with ranking

petter — Sat, 07 Jan 2017 11:36:59 GMT

I don't see why you need to use Agg() at all. That might be the cause of the problem you mention: in 'Small' I always have '-'. The reason is that Aggr() might return 0, 1 or multiple values. And if you get multiple values and you don't have an aggregation function that resolves them into one value to show in one row you will get the '-'.

Try to do as I suggested - just use the Rank() function alone without any Aggr(). You have to use TOTAL inside the Rank() if you have more than one dimension which you obviously have here.

So have a close look at this screenshot an notice the expressions that appears as column headers here:

In the load script I have associated the Biz with numeric values 1, -1, -2, -3 as you mentioned.

Likewise I have associated Deal with number values also in a similar range.

Re: Need help with ranking

Sun, 08 Jan 2017 07:50:52 GMT

Petter,

As I understand, your suggestion is manually assosiate Biz with numeric value - this is not an option for me, as in the real example I have hundreds of deals and busensses.

W/o assosiation I have the following ranking for Biz (please see below) what is incorrect since the right rank for biz: First A (total actual =18), second B (15) and third C (11). So, I need that in the column 'Rabk Biz' there will be something like this (in the considered example):

C 3

A 1

B 2

Re: Need help with ranking

Sun, 08 Jan 2017 07:52:45 GMT

Thank you for reply, but this works in the same way as 'Rank (total [values]) and gives ranking for values, but now I am looking for ranking on Biz and Oppt/Deal level.

Re: Need help with ranking

petter — Sun, 08 Jan 2017 09:10:20 GMT

I don't follow you completely - is there buisnesses called A, B, C and so forth? To me this seems like a demo data set - not something real.

Gettting a sequential numeric value out of the english alphabet from a-z is very straightforward as they occupy the code values from 65 to 90 for uppercase and lettes:

Ord('A') = 65 Ord('Z') = 90

So then it is a matter of just using the Ord()-function. When it comes to Oppt/Deal you probably have to have some kind of mapping table between text and ranking values.