Peek function ?? or Other methods for this question

[jjustingkm]

Hi All,

I have a data set like this.  I am trying to get counts here.

The condition is first get the minimum date for Type = Foreign, In this case , 8/1/2021, then get the counts for all domestic in that group comes after 8/1/21. The count here is 4.

GROUPID	KEY	TYPE	Shipment Date
100	1	DOMESTIC	4/2/2021
100	2	DOMESTIC	4/1/2021
100	3	FOREIGN	2/2/2021
100	4	DOMESTIC	2/1/2021
100	5	DOMESTIC	1/1/2021
100	6	FOREIGN	8/1/2020
100	7	DOMESTIC	1/1/2020

[Quy_Nguyen · Accepted solution]

Hi, not sure it could adapt in your project, from my understanding, I would suggest this:

Data:
Load GROUPID,	
	 KEY,	
     TYPE,	
     Date(Date#([Shipment Date],'D/M/YYYY')) as [Shipment Date];
Load * INLINE
[
GROUPID,	KEY,	TYPE,	Shipment Date
100	,1,	DOMESTIC,	4/2/2021
100	,2,	DOMESTIC,	4/1/2021
100	,3,	FOREIGN,	2/2/2021
100	,4,	DOMESTIC,	2/1/2021
100	,5,	DOMESTIC,	1/1/2021
100	,6,	FOREIGN,	8/1/2020
100	,7,	DOMESTIC,	1/1/2020
];

ForeignMap:
Mapping Load 
	GROUPID, 
    Date(Min([Shipment Date])) as Min_ShipDate
Resident Data where TYPE = 'FOREIGN'
Group by GROUPID;

Result:
Load *,
	 If([Shipment Date] > MinShipDate and TYPE = 'DOMESTIC', 1, 0) as CountFlag;
Load 
	GROUPID,	
    KEY,	
    TYPE, 
    [Shipment Date],
    ApplyMap('ForeignMap',GROUPID, Null()) as MinShipDate
Resident Data;

Drop Table Data;
Drop Field MinShipDate;

Then in you visualization, just Sum the CountFlag field to get the count:

[jjustingkm]

Thanks for the reply Nguyen, I was using something similar, used sql view for temp tables. 

Just thought of checking if we can use peek with range sum in this case.