Solved: Qlik Sense - FirstSortedValue and Aggr - Qlik Community

tomdon14 · ‎2020-03-30

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

tomdon14 · ‎2020-03-31

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/146172...)

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

View solution in original post

sunny_talwar · ‎2020-03-30

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

~~FirstSortedValue(YearMonthNum, -YearMonthNum)~~

tomdon14 · ‎2020-03-30

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	-

sunny_talwar · ‎2020-03-30

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

tomdon14 · ‎2020-03-30

Single row where YearMonth = Nov-2019

Thanks

sunny_talwar · ‎2020-03-30

The remove YearMonth as your dimension...

sunny_talwar · ‎2020-03-30

And use this as your expression

~~FirstSortedValue(YearMonthNum, YearMonthNum)~~

tomdon14 · ‎2020-03-30

Thanks, though still not working.

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

sunny_talwar · ‎2020-03-30

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

tomdon14 · ‎2020-03-30

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

Qlik Sense - FirstSortedValue and Aggr

function

Script and Expressions