topic Calculate First Instance in Set Anlaysis in QlikView

Calculate First Instance in Set Anlaysis

sumeet-vaidya — Thu, 26 Apr 2018 11:59:00 GMT

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

Re: Calculate First Instance in Set Anlaysis

sunny_talwar — Thu, 26 Apr 2018 12:11:18 GMT

Try this

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

Re: Calculate First Instance in Set Anlaysis

sunny_talwar — Thu, 26 Apr 2018 12:13:20 GMT

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];

Re: Calculate First Instance in Set Anlaysis

sumeet-vaidya — Fri, 27 Apr 2018 10:37:29 GMT

Thanks Sunny. Works fine

Re: Calculate First Instance in Set Anlaysis

sunny_talwar — Fri, 27 Apr 2018 11:15:38 GMT

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

Best,

Sunny