[FirstsortedValue] Multiple row, same date

[#1]

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 ?

[its_anandrjs]

Try

Maxstring(Field name)

[stabben23 · Accepted solution]

Hi,

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

[tresesco]

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]

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)

[#6]

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]

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]

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.

[#9]

Thank you also Manish, it seems to work also !

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