Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, all
I'm here with some issue in set analysis.
Imagine I want the following table.
CUST_ID | TXN_DT_ID | STATUS | Remark |
A | 20180802 | N | Excluded |
A | 20180731 | Y | |
B | 20180630 | Y | Included |
C | 20180801 | Y | |
C | 20180730 | N | |
C | 20180804 | N | Excluded |
D | 20180803 | Y | Included |
D | 20180802 | N | |
D | 20180801 | N | |
D | 20180731 | N |
To calculate the distinct count of customer with Status='Y' based on the latest date of each customer only. Based on the data above, my expected result should be 2.
My set analysis, which is wrong somewhere :
count(Aggr(max(If(TXN_DT_ID = FirstSortedValue(TOTAL <CUST_ID> TXN_DT_ID), if(STATUS='Y',CUST_ID))), CUST_ID))
Anyone can help on the set analysis?
Thanks in advance.
Can you try this
sum({< STATUS={'Y'} >}
if(TXN_DT_ID = Aggr(Max({< STATUS= >} TOTAL <CUST_ID> TXN_DT_ID ), CUST_ID), 1, 0)
)
Hi Max,
The value aggregated seems if there're more columns then will be incorrect.
H-m, not quite clear but I have this result