Qlik Community

Ask a Question

Qlik Sense App Development

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

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tomdon14
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
tomdon14
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
Taoufiq_Zarra
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") 😉
tomdon14
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.

Taoufiq_Zarra
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") 😉
tomdon14
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.

Taoufiq_Zarra
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") 😉
tomdon14
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

tomdon14
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