topic [FirstsortedValue] Multiple row, same date in QlikView

[FirstsortedValue] Multiple row, same date

Mon, 31 Mar 2014 10:31:26 GMT

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 ?

Re: [FirstsortedValue] Multiple row, same date

its_anandrjs — Mon, 31 Mar 2014 10:34:48 GMT

Try

Maxstring(Field name)

Re: [FirstsortedValue] Multiple row, same date

stabben23 — Mon, 31 Mar 2014 10:37:56 GMT

Hi,

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

Re: [FirstsortedValue] Multiple row, same date

tresB — Mon, 31 Mar 2014 10:40:37 GMT

Try this:

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

This would give you list of cust_score separated by comma.

Re: [FirstsortedValue] Multiple row, same date

MK_QSL — Mon, 31 Mar 2014 10:52:04 GMT

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)

Re: [FirstsortedValue] Multiple row, same date

Mon, 31 Mar 2014 11:56:39 GMT

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

Re: [FirstsortedValue] Multiple row, same date

MK_QSL — Mon, 31 Mar 2014 12:04:43 GMT

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.

Re: [FirstsortedValue] Multiple row, same date

tresB — Mon, 31 Mar 2014 12:05:59 GMT

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.

Re: [FirstsortedValue] Multiple row, same date

Mon, 31 Mar 2014 13:25:12 GMT

Thank you also Manish, it seems to work also !

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