topic Problem with FirstSortedValue() in QlikView

Problem with FirstSortedValue()

Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I am trying to get the count of incidents using the firstsortedvalue. I have filters for Category and the date. Below is the sample data.

Also, I can not have this at the script as the filters are dynamic.

ID	Group ID	Incident Grade	Incident Time	Incident ID	Incident Date	Incident Date Value	Category
1	80364476	A	00:00:00	95023049	41523	9/6/2013	Public
2	80364476	A	12:24:39	95748468	41564	10/17/2013	Public
3	80364476	A	00:00:00	98735114	41733	4/4/2014	Private
4	80364476	A+	00:00:16	104266044	41960	11/17/2014	Public
5	80364476	A+	00:00:18	107288827	42118	4/24/2015	Private
6	80364476	A+	00:00:31	108508060	42327	11/19/2015	Private
7	80364487	B	00:00:00	95023184	41646	1/7/2014	Public
8	80364487	C	12:24:39	95748603	41687	2/17/2014	Public
9	80364487	C	00:00:00	98735249	41856	8/5/2014	Private
10	80364487	B	00:00:16	104266179	42083	3/20/2015	Public
11	80364487	B	00:00:18	107288962	42241	8/25/2015	Private
12	80364487	C	00:00:31	108508195	42450	12/1/2015	Private

If I select Public and date '12/31/2014' I should see

80364476	A+	104266044	11/17/2014	Public
80364487	C	95748603	2/17/2014	Public

If I select just the date '12/31/2014' and nothing from the Category, then

80364476	A+	104266044	11/17/2014	Public
80364487	C	98735249	8/5/2014	Private

Thanks for all the help.

Re: Problem with FirstSortedValue()

Anonymous — Mon, 21 Mar 2016 15:12:43 GMT

HI,

You are saying you select the date '12/31/2014' and you want as a result dates 11/17/2014, 2/17/2014 or 8/5/2014.

How are you matching those dates?

Please give more details.

Re: Problem with FirstSortedValue()

Mon, 21 Mar 2016 15:14:06 GMT

Get the most recent one. So, in this case incident date less than 12/31/2014.

Re: Problem with FirstSortedValue()

sunny_talwar — Mon, 21 Mar 2016 15:19:12 GMT

Would you be able to share the sample you might be working and testing on? I was wondering how are you making selection in your date field.

Re: Problem with FirstSortedValue()

Mon, 21 Mar 2016 15:22:28 GMT

Using a variable for the date. Will try to upload a sample QVW.
Thanks

Re: Problem with FirstSortedValue()

Anonymous — Mon, 21 Mar 2016 15:24:55 GMT

and why do you get two lines of results? Is it your need?

You don't have to use firstsortedvalue for your need.

You should just sort your table on the Incident Date field "sort by numeric value descending" and in Dimension limits you check "Restrict which values are displayed using the first expression" -> Show only -> First -> 2

Re: Problem with FirstSortedValue()

sunny_talwar — Mon, 21 Mar 2016 15:30:12 GMT

Check out the attached:

No selection in Category and Date = 12/31/2014

Category = Public and Date = 12/31/2014

Re: Problem with FirstSortedValue()

Mon, 21 Mar 2016 16:32:12 GMT

Thanks so much for the reply. I am trying to get the count by the Incident grade (have some others like region etc). In the below case, I need to see

Incident Grade	Count
A	1
C	1

Re: Problem with FirstSortedValue()

sunny_talwar — Mon, 21 Mar 2016 16:34:57 GMT

Not sure, are we talking about the same requirement as above? or the requirement changed?

Re: Problem with FirstSortedValue()

Mon, 21 Mar 2016 16:42:12 GMT

Sorry for the confusion. I used your code to get the count by grade (a different report).

Thanks

Re: Problem with FirstSortedValue()

sunny_talwar — Mon, 21 Mar 2016 16:53:20 GMT

How are you getting a count = 1? Can you elaborate?

Re: Problem with FirstSortedValue()

Mon, 21 Mar 2016 16:58:25 GMT

Desired result is

Incident Grade	Count
A	1
C	1

I am trying this expression. But it is not giving me the right result.

Count( AGGR(

FirstSortedValue({<[Incident Date Value] = {"$(='<' & Date(Max({AS}[Incident Date Value])))"}, [Incident Date]>}[Incident ID], -[Incident Date])

,[Group ID]))

Thanks

Re: Problem with FirstSortedValue()

sunny_talwar — Mon, 21 Mar 2016 17:18:38 GMT

Seems to be working for me:

Re: Problem with FirstSortedValue()

Mon, 21 Mar 2016 17:46:19 GMT

If I use the expression, i get a different Grade and ID.

Thanks

Re: Problem with FirstSortedValue()

sunny_talwar — Mon, 21 Mar 2016 17:50:00 GMT

What did you remove? the dimension?

Re: Problem with FirstSortedValue()

Mon, 21 Mar 2016 17:53:30 GMT

I have attached the QVW.

Re: Problem with FirstSortedValue()

sunny_talwar — Mon, 21 Mar 2016 18:03:10 GMT

See if the attached is helpful

Re: Problem with FirstSortedValue()

Mon, 21 Mar 2016 18:07:35 GMT

The grade should be C for 95748603. But it is showing B.

Re: Problem with FirstSortedValue()

sunny_talwar — Mon, 21 Mar 2016 18:11:18 GMT

If you are going to be showing this in a straight table, I would suggest using [Group ID] as your dimension and hide it. and use three expressions: