show dim with highest value in table (rank + aggr+ concat)

[blaise]

Hi

I have a simple datamodel;

and in a straight table i want to show date as dim, sum(value) as first expression and list the dim value with the highest value as second expression. Similiar to this;

I have an expression that works if i select a single date;

concat(distinct {$<dim = {"=rank(sum(value),0,1)=1"}>} dim,',')

Any ideas how i can make this work? Im thinking about aggr() somewhere but i have tried all combinations i can think of.

Below is the inline script used;

data:
load * inline [
	date,dim,value,highest
	"2019-01-01",alpha,10,N
	"2019-01-01",bravo,20,Y
	"2019-01-01",charlie,20,Y
	"2019-01-02",alpha,10,N
	"2019-01-02",bravo,20,N
	"2019-01-02",charlie,30,Y
];	

[tresesco · Accepted solution]

Try this:

FirstSortedValue(Aggr( Concat(Distinct dim, ','), date,value),-aggr(value,value))

[blaise]

this works fine with the example data but not for my real data (a bit more complex). Im trying to figure out why...