# 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
Did you mean:
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_NUMBER ID_VERSION Timeliness YearMonthNum 12345678901 0 On-Time 201907 12345678901 0 On-Time 201906 12345678901 3 Late 201907 12345678901 3 On-Time 201907 12345678901 5 On-Time 201907 12345678901 10 On-Time 201907 12345678901 14 Late 201910 12345678901 14 On-Time 201910 12345678901 17 Late 201912 12345678901 17 On-Time 201912 12345678901 8 On-Time 201907 12345678901 9 On-Time 201907

 SUMMARY DATA (desired) YearMonthNum Late Total 201906 1 1

Labels (2)

• ### aggregation

1 Solution

Accepted Solutions
Contributor III

Thank you! Resolved it as follows:

 YearMonth sum(Aggr(if(YearMonthNum = Min({} TOTAL YearMonthNum),1,0),CASE_AER_NUMBER_VERSION, YearMonthNum)) sum(Aggr(if(YearMonthNum = Min({} TOTAL YearMonthNum),1,0),CASE_AER_NUMBER_VERSION, YearMonthNum)) Oct-2015 1 1 Nov-2015 0 1 Dec-2015 1 0
7 Replies
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

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

both are possible

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

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

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

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.

Contributor III

Thank you! Resolved it as follows:

 YearMonth sum(Aggr(if(YearMonthNum = Min({} TOTAL YearMonthNum),1,0),CASE_AER_NUMBER_VERSION, YearMonthNum)) sum(Aggr(if(YearMonthNum = Min({} TOTAL YearMonthNum),1,0),CASE_AER_NUMBER_VERSION, YearMonthNum)) Oct-2015 1 1 Nov-2015 0 1 Dec-2015 1 0
Tags