topic Re: FirstSortedValue returns Null in QlikView

FirstSortedValue returns Null

kdsonntag — Mon, 06 Aug 2018 20:13:12 GMT

I am having trouble getting the desired output. I need an expression that gives me Score on most recent Date for each Client.

Sample

Client Score Date

Aaa 25 1/1/18

Aaa 24 5/1/18

Bbb 24 5/1/18

Bbb 20 2/1/18

Should return

Aaa 24 5/1/18

Bbb 24 5/1/18

If I don't use DISTINCT, get NULL

If I DO use DISTINCT, only get Aaa record, not Bbb.

What I'm currently using: FirstSortedValue(Score, -Date) or FirstSortedValue(DISTINCT Score, -Date)

sunny_talwar — Mon, 06 Aug 2018 20:15:28 GMT

Are you using Client as dimension? If you are... then this should not be a problem... if you are using this in a KPI object... try this

Sum(Aggr(FirstSortedValue(Score, -Date), Client))

kdsonntag — Mon, 06 Aug 2018 20:22:51 GMT

Yes, Client as Dimension and used in KPI Object.

Here's my expression: =Sum(Aggr(FirstSortedValue([Score],-[Date]), [Client])

Returns nothing.

sunny_talwar — Mon, 06 Aug 2018 20:27:37 GMT

You have a missing parenthesis at the end... is that a typo?

=Sum(Aggr(FirstSortedValue([Score],-[Date]), [Client]))

kdsonntag — Mon, 06 Aug 2018 20:34:04 GMT

Yes, typo. Sorry about that.

sunny_talwar — Mon, 06 Aug 2018 21:00:46 GMT

Don't see any issue with this expression

kdsonntag — Mon, 06 Aug 2018 21:05:47 GMT

Now I get all zero.

Aaa 0

Bbb 0

Here is expression with actual fields. Sorry, do not know how to export Qlik file.

=Sum(Aggr(FirstSortedValue([PHQ9.Total Score],-[PHQ9.Assessment Date]),[PHQ9.Client Name]))

sunny_talwar — Tue, 07 Aug 2018 00:58:49 GMT

Unless you are able to share more information, I won't know what you have.

kdsonntag — Tue, 07 Aug 2018 15:12:02 GMT

Thank you for trying to help. I have contacted the developer of our application for additional support and will post again when I have more info.