topic Re: Qlik Sense - FirstSortedValue and Aggr in App Development

Qlik Sense - FirstSortedValue and Aggr

tomdon14 — Sat, 16 Nov 2024 02:50:23 GMT

Hello - having an issue with FirstSortedValue and Aggr function. In the example below, I'm trying to identify the earliest month. I'm expecting that FirstSortedValue should be 201911 but formula is returning 201912. Any ideas? Thanks.

CASE_AER_NUMBER_VERSION	YearMonthNum	aggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION)	FirstSortedValue( YearMonthNum, aggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION))
20151234567 (55)	201911	201911	-
20151234567 (55)	201912	-	201912

Re: Qlik Sense - FirstSortedValue and Aggr

sunny_talwar — Mon, 30 Mar 2020 17:49:00 GMT

Will you have YearMonthNum as a dimension in the chart? If not, then try this

~~FirstSortedValue(YearMonthNum, -YearMonthNum)~~

Re: Qlik Sense - FirstSortedValue and Aggr

tomdon14 — Mon, 30 Mar 2020 16:24:02 GMT

Thanks Sunny. I changed the dimension and updated the formula but still seeing incorrect results.

CASE_AER_NUMBER_VERSION	YearMonth	aggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION)	FirstSortedValue(YearMonthNum, -YearMonthNum)
20151234567 (55)	Dec-2019	-	201912
20151234567 (55)	Nov-2019	201911	-

Re: Qlik Sense - FirstSortedValue and Aggr

sunny_talwar — Mon, 30 Mar 2020 16:33:49 GMT

Are you looking to see a single row where YearMonth = Nov-2019? or are you looking to see two rows, both with 201911?

Re: Qlik Sense - FirstSortedValue and Aggr

tomdon14 — Mon, 30 Mar 2020 17:23:24 GMT

Single row where YearMonth = Nov-2019

Thanks

Re: Qlik Sense - FirstSortedValue and Aggr

sunny_talwar — Mon, 30 Mar 2020 17:25:36 GMT

The remove YearMonth as your dimension...

Re: Qlik Sense - FirstSortedValue and Aggr

sunny_talwar — Mon, 30 Mar 2020 17:48:27 GMT

And use this as your expression

~~FirstSortedValue(YearMonthNum, YearMonthNum)~~

Re: Qlik Sense - FirstSortedValue and Aggr

tomdon14 — Mon, 30 Mar 2020 17:38:09 GMT

Thanks, though still not working.

CASE_AER_NUMBER_VERSION	aggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION)	FirstSortedValue(YearMonthNum, YearMonthNum)
20151234567 (55)	201911	-

Re: Qlik Sense - FirstSortedValue and Aggr

sunny_talwar — Mon, 30 Mar 2020 17:47:57 GMT

I am sorry, can you just try this Min(YearMonthNum)

Re: Qlik Sense - FirstSortedValue and Aggr

tomdon14 — Mon, 30 Mar 2020 18:04:00 GMT

Thanks Sunny. That did get the earliest month.

CASE_AER_NUMBER_VERSION	aggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION)	min(YearMonthNum)
20151234567 (55)	201911	201911

But how do I now get a count of all of those cases that occur in the earliest month. In this case, I want to count Nov but not Dec. (That's why I was trying to use FirstSortedValue with Aggr)

CASE_AER_NUMBER_VERSION	YearMonthNum
20151234567 (55)	201911
20151234567 (55)	201912

Re: Qlik Sense - FirstSortedValue and Aggr

tomdon14 — Tue, 31 Mar 2020 15:55:35 GMT

Thanks Sunny. I resolved it as follows with help from a different post of yours 😊:

(https://community.qlik.com/t5/QlikView-Creating-Analytics/Using-set-analysis-aggregation/td-p/1461721)

YearMonth	CASE_AER_NUMBER_VERSION	sum(Aggr(if(YearMonthNum = Min({<YearMonth=>} TOTAL <CASE_AER_NUMBER_VERSION> YearMonthNum),1,0),CASE_AER_NUMBER_VERSION, YearMonthNum))
Nov-2019	20151234567 (55)	1