Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik GA: Multivariate Time Series in Qlik Predict: Get Details
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
tomdon14
Contributor III
Contributor III
‎2020-03-30 12:10 PM

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)
Labels
2,171 Views
0 Likes
1 Solution

Accepted Solutions
tomdon14
Contributor III
Contributor III
‎2020-03-31 11:55 AM
Author

In response to tomdon14

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

2,125 Views
0 Likes
10 Replies
sunny_talwar
MVP
MVP
‎2020-03-30 12:15 PM

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

FirstSortedValue(YearMonthNum, -YearMonthNum)

 

 

1,866 Views
0 Likes
tomdon14
Contributor III
Contributor III
‎2020-03-30 12:24 PM
Author

In response to sunny_talwar

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-
1,863 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-03-30 12:33 PM

In response to tomdon14

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

1,860 Views
0 Likes
tomdon14
Contributor III
Contributor III
‎2020-03-30 01:23 PM
Author

In response to sunny_talwar

Single row where YearMonth = Nov-2019

Thanks

1,843 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-03-30 01:25 PM

In response to tomdon14

The remove YearMonth as your dimension...

1,841 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-03-30 01:26 PM

In response to sunny_talwar

And use this as your expression

FirstSortedValue(YearMonthNum, YearMonthNum)

 

1,840 Views
0 Likes
tomdon14
Contributor III
Contributor III
‎2020-03-30 01:38 PM
Author

In response to sunny_talwar

Thanks, though still not working.

CASE_AER_NUMBER_VERSIONaggr(min(YearMonthNum), CASE_AER_NUMBER_VERSION)FirstSortedValue(YearMonthNum, YearMonthNum) 
20151234567 (55)201911-
1,837 Views
0 Likes
sunny_talwar
MVP
MVP
‎2020-03-30 01:47 PM

In response to tomdon14

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

1,835 Views
0 Likes
tomdon14
Contributor III
Contributor III
‎2020-03-30 02:04 PM
Author

In response to sunny_talwar

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
1,831 Views
0 Likes