Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be FirstSortedValue() function:
FirstSortedValue(merchant, -Aggr(Rank(-min(price), 1, 1), product_id, merchant))
Only({<Field={'value'}>} aggr(rank(sum({1}Sales)), Field)
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))
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?
May be this:
=Concat(If(Aggr(Rank(-Min(price), 1, 1), product_id, merchant) = 1, merchant), ',')
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.
😕
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), ',')
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.