How to unique count?

[Anonymous]

I have these sets of data where the  masterdate is mapped to a master calendar.

CLAIM_TRANNO	Claim_Code	Claim_Type	CLAIMNUMBER	MasterDate	Own_Damaged_Count	Property_Damage_Count
1	PVCAF	OD_FEE	EIBV9074913PLM	01/12/2012	0	0
1	PVCRC	OD	EIBV9074913PLM	01/12/2012	1	0
2	PVCAF	OD_FEE	EIBV9074913PLM	01/01/2013	0	0
2	PVCRC	OD	EIBV9074913PLM	01/01/2013	1	0
3	PVCAF	OD_FEE	EIBV9074913PLM	01/01/2013	0	0
3	PVCRC	OD	EIBV9074913PLM	01/01/2013	1	0
4	PVCRC	OD	EIBV9074913PLM	01/03/2013	1	0
5	PVCRC	OD	EIBV9074913PLM	01/04/2013	1	0
5	PVTRC	TPPD	EIBV9074913PLM	01/04/2013	0	1
6	PVCRC	OD	EIBV9074913PLM	01/04/2013	1	0
6	PVTRC	TPPD	EIBV9074913PLM	01/04/2013	0	1
7	PVCRC	OD	EIBV9074913PLM	01/04/2013	1	0
8	PVTRC	TPPD	EIBV9074913PLM	01/06/2014	0	1
9	PVCAF	OD_FEE	EIBV9074913PLM	01/06/2014	0	0
9	PVCRC	OD	EIBV9074913PLM	01/06/2014	1	0
9	PVTRC	TPPD	EIBV9074913PLM	01/06/2014	0	1
10	PVCCT	OD	EIBV9074913PLM	01/06/2015	1	0
10	PVTRC	TPPD	EIBV9074913PLM	01/06/2015	0	1
11	PVCCT	OD	EIBV9074913PLM	01/06/2015	1	0
11	PVTRC	TPPD	EIBV9074913PLM	01/06/2015	0	1
13	PVCCT	OD	EIBV9074913PLM	01/06/2015	1	0
13	PVTRC	TPPD	EIBV9074913PLM	01/06/2015	0	1

For this claim number, i want to unique count the claim type based on OD and TPPD only.

The results should be like this in pivot table

                   Year Month

                    201202 201301 201304 201406 201506

no of claim        1                        1

How do i use set analysis to calculate this?

[Anonymous]

I got this results.

Year	2012	2013	2013	2014	2015
Month	Dec	Jan	Apr	Jun	Jun
No of Claims	1	1	1	1	1

[avinashelite]

Thanks for the clarification sir

[Anonymous]

Hi

try this

=Count(Distinct({<CLAIMTYPE={'OD','TPPPD'}>}CLAIMNUMBER)

[Anonymous]

It returns 0.

=Count(Distinct({<CLAIMTYPE={'OD','TPPD'}>}CLAIMNUMBER)

Month	Dec	Jan	Apr	Jun	Jun
No of Claims	0	0	0	0	0

[Peter_Cammaert]

How do you get 201202 = 1 in your Pivot when there is no data for that period?

[Peter_Cammaert]

A minor one, tres. The first Claim_Type should be OD, not OP. Funny how all other responses except for the one from rgvavihs copied your example.

[Anonymous]

Month	Count(DISTINCT{<CLAIM_TYPE={'OD','TPPD'}>}CLAIMNUMBER)
	1
01	1
03	1
04	1
06	1
12	1

[Peter_Cammaert]

So you mean that you want to count individual CLAIMNUMBERS that have both Claim_Types OD and TPPD in the same period? Or on the same day? And count all occurrences in the same period for a particular CLAIMNUMBER as one?

And where is the data for Feb 2012? Can we add it ourselves?

[Anonymous]

Hi Peter,

     There's a typo there. it should be

Year Month

                    201212 201301 201304 201406 201506

no of claim        1                        1

The data is on transaction basis. What i need to count is for this claim there are OD and TPPD claim reported. So OD claim was first transacted on 201212 and TPPD claim was first transacted on 201304. The remaining transaction are just movements of the ClaimNumber.

[Anonymous]

And count all occurrences in the same period for a particular CLAIMNUMBER as one?

I think this is the one that i need. Means for this claimnumber. There are 1 od and 1 tppd. However it need to reflects to its first transacted month.