Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Qlik Sense - FirstSortedValue and Aggr

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_VERSIONYearMonthNumaggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION)FirstSortedValue(  YearMonthNum, aggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION))
20151234567 (55)201911201911-
20151234567 (55)201912-201912
Labels (2)
1 Solution

Accepted Solutions
Contributor III
Contributor III

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

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

View solution in original post

10 Replies

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

FirstSortedValue(YearMonthNum, -YearMonthNum)

 

 

Contributor III
Contributor III

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

CASE_AER_NUMBER_VERSIONYearMonthaggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION)FirstSortedValue(YearMonthNum, -YearMonthNum) 
20151234567 (55)Dec-2019-201912
20151234567 (55)Nov-2019201911-

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

Contributor III
Contributor III

Single row where YearMonth = Nov-2019

Thanks

The remove YearMonth as your dimension...

And use this as your expression

FirstSortedValue(YearMonthNum, YearMonthNum)

 

Contributor III
Contributor III

Thanks, though still not working.

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

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

Contributor III
Contributor III

Thanks Sunny. That did get the earliest month.

CASE_AER_NUMBER_VERSIONaggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION)min(YearMonthNum)
20151234567 (55)201911201911

 

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_VERSIONYearMonthNum
20151234567 (55)201911
20151234567 (55)201912