Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 |
on load script or UI ?
how you get 1 for late and 1 for total ?
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.
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)
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.
I didn't fully understand it.
Can you share a capture of an example
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.
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 |