Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

[FirstsortedValue] Multiple row, same date

Hi,

I'm currently working on a graph that will show the score of customer. It's just a field, link with some date. The users want to see the last value available, so i'm using

=FirstSortedValue(CUST_SCORE, -Date)

It works but the problem is, sometimes, there is multiple row with the same date (and same score) so it shows "-".

I tried ONLY() but the expression didn't work at all... Do you have an idea ?

1 Solution

Accepted Solutions
stabben23
Partner - Master
Partner - Master

Hi,

use FirstSortedValue(distinct CUST_SCORE, -Date), this will gives you syntax wrong, but it works.

View solution in original post

8 Replies
its_anandrjs
Champion III
Champion III

Try

Maxstring(Field name)

stabben23
Partner - Master
Partner - Master

Hi,

use FirstSortedValue(distinct CUST_SCORE, -Date), this will gives you syntax wrong, but it works.

tresesco
MVP
MVP

Try this:

=FirstSortedValue( Aggr(Concat(distinct CUST_SCORE, ',') , CUST_SCORE), -Aggr(Date,Date))

This would give you list of cust_score separated by comma.

MK_QSL
MVP
MVP

If you have same score on same date and you want min(Score) from that, use below..

FirstSortedValue(DISTINCT Aggr(Min(CUST_SCORE),Date),-Date)

Use Max if you want Max score for any date..

FirstSortedValue(DISTINCT Aggr(Max(CUST_SCORE),Date),-Date)

Not applicable
Author

Hi guys,

Thank you all for the answers ! It seems the best in this case is the Staffan solution !

But  i'm really interested in tresesco expression, it doesn't work though, are you sure about syntax ?

Thank you again

MK_QSL
MVP
MVP

Try my code also.

FirstSortedValue(distinct CUST_SCORE, -Date)

Above code, will give you min(CUST_SCORE) if there are more than one CUST_SCORE on same date...

In case, if you want max, you can use the above solution provided by myself.

tresesco
MVP
MVP

You are right, I did a little mistake in the expression.

Corrected one:

FirstSortedValue( Aggr(Concat(distinct CUST_SCORE, ',') , Date), -Aggr(Date,Date))

Aggregation had to be on Date field.

Not applicable
Author

Thank you also Manish, it seems to work also !

But the perfect answer was FirstSortedValue(distinct CUST_SCORE, -Date)