Qlik Sense - Aggregation Challenge

[tomdon14]

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

[Taoufiq_Zarra]

on load script or UI ?

how you get 1 for late and 1 for total ?

[tomdon14]

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]

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)

[tomdon14]

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]

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

[tomdon14]

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.

[tomdon14 · Accepted solution]

Thank you! Resolved it as follows:

YearMonth	sum(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-2015	1	1
Nov-2015	0	1
Dec-2015	1	0