topic Qlik Sense - Aggregation Challenge in App Development

Qlik Sense - Aggregation Challenge

tomdon14 — Sat, 16 Nov 2024 02:55:52 GMT

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

Re: Qlik Sense - Aggregation Challenge

Taoufiq_Zarra — Fri, 20 Mar 2020 13:26:25 GMT

on load script or UI ?

how you get 1 for late and 1 for total ?

Re: Qlik Sense - Aggregation Challenge

tomdon14 — Fri, 20 Mar 2020 13:40:28 GMT

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.

Re: Qlik Sense - Aggregation Challenge

Taoufiq_Zarra — Fri, 20 Mar 2020 19:54:17 GMT

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)

Re: Qlik Sense - Aggregation Challenge

tomdon14 — Mon, 23 Mar 2020 19:01:08 GMT

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.

Re: Qlik Sense - Aggregation Challenge

Taoufiq_Zarra — Mon, 23 Mar 2020 19:31:53 GMT

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

Re: Qlik Sense - Aggregation Challenge

tomdon14 — Mon, 23 Mar 2020 19:46:14 GMT

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.

Re: Qlik Sense - Aggregation Challenge

tomdon14 — Tue, 31 Mar 2020 15:50:00 GMT

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