topic Re: FirstSortedValue issue in QlikView

FirstSortedValue issue

Anonymous — Wed, 01 May 2013 17:13:46 GMT

Hey all,

I'm having difficulty getting values using FirstSortedValue. I'm trying to retrieve the latest "amount" per record but I am getting null values and I'm not sure why. Here's what my script looks like:

=FirstSortedValue(amount, -date)

I would greatly appreciate the help.

Thanks,

Napac

*EDIT*

I feel I should clarify what I'm looking for. I'm trying to extract the latest total amount per account.

Account Amount Date

1 25 4/30/13

2 432 4/30/13

2 324 4/30/13

2 381 5/1/13

1 89 5/1/13

1 9821 5/1/13

2 423 5/2/13

For instance, I would want to sum the latest amounts for accounts 1 and 2 without bringing in any amounts before the latest date

Re: FirstSortedValue issue

Anonymous — Wed, 01 May 2013 17:35:21 GMT

Napac,

The expression looks fine. The problem could be because of the relations between amount and date. Are they in the same logical table? Is there an amount for every date? Are there multiple amount values in one date? How many amount values available if you select the latest date?

Regards,

Michael

Re: FirstSortedValue issue

Anonymous — Thu, 02 May 2013 13:50:03 GMT

There are multiple values for the data, I'm trying to sum the values for the latest date per account

Re: FirstSortedValue issue

Anonymous — Thu, 02 May 2013 14:07:25 GMT

I've also tried Last value as such:

table_2:

load

LastValue("total_amount") as Latest_total

resident table_1;

Re: FirstSortedValue issue

anandathome — Thu, 02 May 2013 14:18:11 GMT

Hi Napac,

Could you check what format you date is in?

Convert it to a Numeric value like 20130502 for 02-May-2013

and check.

Regards,

Anand.

Re: FirstSortedValue issue

Anonymous — Thu, 02 May 2013 16:02:48 GMT

Still nothing 😕

I feel I should clarify what I'm looking for. I'm trying to extract the latest total amount per account.

Account Amount Date

1 25 4/30/13

2 432 4/30/13

2 324 4/30/13

2 381 5/1/13

1 89 5/1/13

1 9821 5/1/13

For instance, I would want to sum the latest amounts for accounts 1 and 2 without bringing in anything before the latest date

Re: FirstSortedValue issue

rwunderlich — Thu, 02 May 2013 18:53:09 GMT

Under the rules of FIrstSortedValue, you will get null when the more than one row is returned. So for example you will get null for account 1 because it has two rows for the last date - 5/1/13. You can fix that in two ways.

1. Add the distinct keyword

=FirstSortedValue(DISTINCT amount, -date)

I'm not sure what the rules are for how distinct decides which row to pick.

2. Add a "bias" to force the sort-weights to be unequal"

=FirstSortedValue(DISTINCT amount, -date + (amount*.00001))

That will pick the largest value on the last date.

If you want to select the last row in load order, asign a "recno() as recid" counter to the table at load time and use

=FirstSortedValue(DISTINCT amount, -date + (recid*.00001))

-Rob

Re: FirstSortedValue issue

rustyfishbones — Thu, 02 May 2013 21:40:01 GMT

Maybe this video on YouTube could help

http://youtu.be/at9riDWED4Q

Regards

Alan

Re: FirstSortedValue issue

Fri, 03 May 2013 05:46:06 GMT

LOAD Account,

Date(max(Date1),'DD-MM-YYYY') as maxdate,

FirstSortedValue(Amount,-Date1) as Total_Amount

Where Account <=2

Group BY Account;

LOAD * INLINE [

Account, Amount, Date1

1, 25, 4/30/13

2, 432, 4/30/13

2, 324, 4/30/13

2, 381, 5/1/13

1, 89, 5/1/13

1, 9821, 5/1/13

2, 423, 5/2/13

];

Re: FirstSortedValue issue

swuehl — Fri, 03 May 2013 14:06:24 GMT

Hi Napac,

I read your request that you want to group your data per Account and Date, but only for the latest date per Account.

I think you can do it like this:

Create a straight table chart with dimension Account, and then create an expression like

=FirstSortedValue( aggr(sum(Amount),Account,Date), -aggr(Date,Account,Date) )

You can create an additional expression

=max(Date)

to show also the latest date per Account in the chart.

Take care that your Date field is interpreted by QV as Date, having a numerical representation.

Re: FirstSortedValue issue

Anonymous — Mon, 06 May 2013 13:30:05 GMT

This works, thank you so much!

Re: FirstSortedValue issue

jolivares — Fri, 21 Jun 2013 21:53:13 GMT

Hi Swuehl, I have a similar problem and try to adapt you solution but with not result... may you check this.

Re: FirstSortedValue issue

swuehl — Fri, 21 Jun 2013 22:51:36 GMT

The sort weight (second argument to FirstSortedValue() function) needs to be numeric.

So you should create your year-month field AM using a QV date function, not using string operations.

See attached.

Re: FirstSortedValue issue

pablolabbe — Thu, 03 Jul 2014 20:03:58 GMT

Hi Rob,

Your tip about duplicates on sort column helped me and my coleague to solve a customer issue.

Thank you,

Pablo Labbe

Re: FirstSortedValue issue

sujeetsingh — Fri, 10 Oct 2014 12:49:57 GMT

Samuel can you please explain that how this works in ?

Re: FirstSortedValue issue

Tue, 05 Apr 2016 09:03:01 GMT

Hi everyone,

I make this works fine when i'm on the application on my computer, but as soon as i distribute it on the server, it shows random data, like if the firstsortedvalue() doesn't work on ajax.

=FirstSortedValue(DISTINCT aggr(count({<[APP STATUS]={'Deployed'},[APP ENVIRONNEMENT]={'Production'},[PERSONROLE]={'Supportedby'}>} DISTINCT [APP ID]),Date),-Date)

Do you have any idea ? I'm getting crazy here

Re: FirstSortedValue issue

bsbernabe — Thu, 05 Jan 2017 07:03:27 GMT

Hi Swuehl, I have a also similar problem for this, but the different i have is IDType, so im trying to use your sample its working but i need to add the ITDType.

=If (IDType= 2,FirstSortedValue( aggr(sum(RATING),HEADERID,CREATEDDATE), -aggr(CREATEDDATE,HEADERID,CREATEDDATE) ),PARTTYPE)

Note: Can you correct me fro this expression.

Thanks Bing