Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
blaise
Partner - Specialist
Partner - Specialist

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

Hi

I have a simple datamodel;

clipboard_image_0.png

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;

clipboard_image_1.png

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
];	
Labels (4)
1 Solution

Accepted Solutions
tresesco
MVP
MVP

Try this:

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

Capture.PNG

View solution in original post

2 Replies
tresesco
MVP
MVP

Try this:

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

Capture.PNG

blaise
Partner - Specialist
Partner - Specialist
Author

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