Calculate First Instance in Set Anlaysis

[sumeet-vaidya]

Dear all,

Please find below scenario where need to count no. of policices day wise.

Input     

PolicyNo	Status	Date	Issue Date
1001	A	1/8/2017	1/1/2017
1001	B	1/2/2017	1/1/2017
1001	B	1/3/2017	1/1/2017
1001	A	1/1/2017	1/1/2017
1001	B	1/5/2017	1/1/2017
1002	A	1/3/2017	1/1/2017
1002	B	1/2/2017	1/1/2017
1003	A	1/1/2017	1/1/2017
1004	A	1/1/2017	1/1/2017

Need to Pick the count of the Policy for the first instance of its Status w.r.t Issue Date

Output   

Issue Date	Total Count	Count A	Count B
1/1/2017	4	3	1

stalwar1‌

Regards

Sumeet

[sunny_talwar]

Try this

=Sum(Aggr(If(Date = Min(TOTAL <PolicyNo, [Issue Date]> Date), 1, 0), [Issue Date], PolicyNo, Status, Date))

[sunny_talwar · Accepted solution]

Or this

=Count(DISTINCT {<Flag = {1}>} PolicyNo)

Where Flag is created in the script like this

Table:

LOAD * INLINE [

    PolicyNo, Status, Date, Issue Date

    1001, A, 1/8/2017, 1/1/2017

    1001, B, 1/2/2017, 1/1/2017

    1001, B, 1/3/2017, 1/1/2017

    1001, A, 1/1/2017, 1/1/2017

    1001, B, 1/5/2017, 1/1/2017

    1002, A, 1/3/2017, 1/1/2017

    1002, B, 1/2/2017, 1/1/2017

    1003, A, 1/1/2017, 1/1/2017

    1004, A, 1/1/2017, 1/1/2017

];

Left Join (Table)

LOAD [Issue Date],

[PolicyNo],

Min(Date) as Date,

1 as Flag

Resident Table

Group By [Issue Date], [PolicyNo];

[sumeet-vaidya]

Thanks Sunny. Works fine

[sunny_talwar]

Just fine? I wish it worked better .... I am glad that it worked out

Best,

Sunny