Qlik Community

Ask a Question

Qlik Sense App Development

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

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Qlik Sense - Aggregation Challenge

Hello – hoping someone can provide insight on an aggregation challenge. In the example below, I need to identify the ID_NUMBER in the oldest month and count it only once. I also need to check if there are any LATE versions for the ID_NUMBER, regardless of which month they fall into, and count it once with the oldest month. The summary data should appear as in Summary Data below.

I would also like to use YearMonthNum as a dimension that can be selected for filtering.

Thanks in advance for any assistance!

DETAIL DATA   
ID_NUMBERID_VERSIONTimelinessYearMonthNum
123456789010On-Time201907
123456789010On-Time201906
123456789013Late201907
123456789013On-Time201907
123456789015On-Time201907
1234567890110On-Time201907
1234567890114Late201910
1234567890114On-Time201910
1234567890117Late201912
1234567890117On-Time201912
123456789018On-Time201907
123456789019On-Time201907

 

SUMMARY DATA (desired) 
YearMonthNumLateTotal
20190611



Labels (2)
1 Solution

Accepted Solutions
Contributor III
Contributor III

Thank you! Resolved it as follows:

YearMonthsum(Aggr(if(YearMonthNum = Min({<COMR_LATENESS_FLAG_LABEL = {'Late'}, YearMonth=>} TOTAL <CASE_AER_NUMBER_VERSION> YearMonthNum),1,0),CASE_AER_NUMBER_VERSION, YearMonthNum))sum(Aggr(if(YearMonthNum = Min({<YearMonth=>} TOTAL <CASE_AER_NUMBER_VERSION> YearMonthNum),1,0),CASE_AER_NUMBER_VERSION, YearMonthNum))
Oct-201511
Nov-201501
Dec-201510

View solution in original post

7 Replies
Master II
Master II

on load script or UI ?

how you get 1 for late and 1 for total ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Contributor III
Contributor III

Thanks Toufiq. Trying to do this in the UI (but open to other options.)

1 for Total: distinct count of ID_NUMBER  - assigned to oldest YearMonthNum (201906)

1 for Late: if any of the versions for ID_NUMBER 12345678901 are Late, then count the entire ID_NUMBER as Late. In this example, versions 3, 14 and 17 are late but we only count it as one late instance.

Thanks for any insight.

Master II
Master II

both are possible

Capture.PNG

UI for example :

 

YearMonthNum =

 

=aggr(Min(YearMonthNum),ID_NUMBER)

 

Expression :

Total:

 

=count({<YearMonthNum={"$(=Min(YearMonthNum))"}>} distinct ID_NUMBER)

 

 

Late:

 

=count({<Timeliness={'Late'},ID_NUMBER={"$(=FirstSortedValue(ID_NUMBER,YearMonthNum))"}>} distinct ID_NUMBER)

 

 

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Contributor III
Contributor III

Thanks Taoufiq. This seems to work. However when I select the YearMonthNum dimension to filter, it  now displays the list of ID_NUMBER's in the filter selection bar instead of the YearMonthNum. Any ideas on how I can get it to display the YearMonthNum? (I have tried to create a Master Dimension without success.) Thanks.

Master II
Master II

I didn't fully understand it.
Can you share a capture of an example

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Contributor III
Contributor III

Thanks, please see below. 

Submission Month dimension is calculated as "=date(Aggr(Min(Date#(YearMonthNum, 'YYYYMM')),CASE_AER_NUMBER_VERSION),'MMM-YYYY')"

When I select a particular Submission Month, the month is not displayed in the filter but rather the filter of case numbers.

Capture.PNG

Contributor III
Contributor III

Thank you! Resolved it as follows:

YearMonthsum(Aggr(if(YearMonthNum = Min({<COMR_LATENESS_FLAG_LABEL = {'Late'}, YearMonth=>} TOTAL <CASE_AER_NUMBER_VERSION> YearMonthNum),1,0),CASE_AER_NUMBER_VERSION, YearMonthNum))sum(Aggr(if(YearMonthNum = Min({<YearMonth=>} TOTAL <CASE_AER_NUMBER_VERSION> YearMonthNum),1,0),CASE_AER_NUMBER_VERSION, YearMonthNum))
Oct-201511
Nov-201501
Dec-201510

View solution in original post