How to get the name of Rank 1 candidate?

[#1]

I use a Rank function to determine which merchant has the cheapest price for a specific product.

I am able to have merchants as dimension and use Rank inside of an Aggr to count how much product a merchant has on Rank 1, 2, 3 ... etc.

But now I need something different.

I have the products as dimension in a table. And I want to create a column which gives me that merchant which is on Rank 1.

I thought about using this:

If ( aggr ( rank ( -min(price),1,1), product_id, merchant) , merchant)

but this doesn't work as intended.

neither does:

Only ( {pseudo-code: where rank = 1} merchant)

I have no clue how to achieve this.

[sunny_talwar]

May be FirstSortedValue() function:

FirstSortedValue(merchant, -Aggr(Rank(-min(price), 1, 1), product_id, merchant))

[Chanty4u]

Only({<Field={'value'}>} aggr(rank(sum({1}Sales)), Field)

[swuehl]

I think there is one minus too many, Sunny.

And I would add the DISTINCT qualifier expecting multiple records in the aggregation scope for each merchant:

FirstSortedValue(DISTINCT merchant, Aggr(Rank(-min(price), 1, 1), product_id, merchant))

[#5]

Ok, thanks for that. It is going in the right direction.

The only problem: I have more merchants sharing one rank.

I don't want to pick one "random" to mark as Position 1.

And I don't want to change the Rank parameter (so that the rank counts on when the same value exists) because it will lead to the same "randomness"

Is there a way to Concat those merchants which share the same rank?

[sunny_talwar]

May be this:

=Concat(If(Aggr(Rank(-Min(price), 1, 1), product_id, merchant) = 1, merchant), ',')

[#7]

Thx alot so far.

I am almost there.

It works like a charm when i have one date as current selection.

but now i am trying to get this (last date) into the right spot(s) as set expression.

{ $ <filter_date = {'$(=date(floor(Max( {$<[filter_date]=>} discoverydate))))'} > }

This is my set expression.

I think i tried any possible place to put it, put it in more places too, but I don't get it working.

😕

[sunny_talwar]

May be this:

=Concat({$<filter_date = {'$(=Date(Floor(Max({$<[filter_date]=>} discoverydate))))'}>} If(Aggr(Rank(-Min({$<filter_date = {'$(=Date(Floor(Max({$<[filter_date]=>} discoverydate))))'}>} price), 1, 1), product_id, merchant) = 1, merchant), ',')

[#9]

i tried the set expr. in the aggr and in the min

which has the same effect as your suggestion.

but i think i know why some products don't have a position.

they hav no offers at the given date.

I thought, that this inner:

{'$(=Date(Floor(Max({$<[filter_date]=>} discoverydate))))'}

would get the max date in respect of the given product_id - dimension, and thus could be able to find the max date for this products, but somehow i guess it is allways the "total" max and thus I have some rows without data.