How do you mimic SQL Dense_Rank function using set analysis?

[Anonymous]

Hello,

I am using the following expression to rank average values by group.

=Only({$<OrganizationID={$(vOrganizationID)}>} aggr(Rank(TOTAL -Avg(value)), group))

This works except for when the average of the value is the same. It assigns the values the same rank but skips numbers after that. Here is an example:

1

2

3

3

5

6

I would like instead have it look like this:

1

2

3

3

4

5

Can anybody help me out with this?

Thanks!

[vishsaggi]

can you share some sample data to look into?

May be check here if this helps:

Dense Rank in QlikView | Qlik Community

[Anonymous]

I am updating the expression because the one I posted is incorrect. The correct expression is as follows:

=Only({$<goupID={$(vGroupID)}>} aggr(Rank(TOTAL -Avg(value)), groupID))

Sorry for the confusion.

[Anonymous]

Here is what the example data would look like. I am calculating the average of the value field and returning the rank of the groupID that is set in the vGroupID variable based on the average.

Load * inline

[

groupID,value

1,100

1,0

1,100

2,0

2,0

2,100

3,100

3,0

3,100

4,100

4,100

4,100

];

[vishsaggi]

can you share your variable expression?

[Anonymous]

It is not an expression. It is set in a Web UI and will always match one of the groupID values shown in the example data.

[Anonymous]

Hey Vishwarath, just wondering if you had any luck solving this issue?

[vishsaggi]

Hey Sorry was away could not work on this. Will look into it.