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?

[tresesco]

Assuming you have YearMonth field in master calendar.

Chart:   Pivot

Dim:     YearMonth

Exp:      Count( Distinct {<Claim_Type={OP, TPPD}>}Claim_Type)

Drag the dimension to top right.

[Anonymous]

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

im getting this result using your script above.

what i need  the od and tppd should be count as 1 and 1 based on date its transacted.

Year Month 

                    201202 201301 201304 201406 201506

no of claim        1                        1

[avinashelite]

Hi

tresesco‌ sir, I think quotes need to be add rite {'OP','TPPD'}

try like this:

Count( Distinct {<Claim_Type={'OP','TPPD'}>}Claim_Type)

[SreeniJD]

use this expression

count( distinct aggr( {<ClaimType = {'OP','TPPD'}>}ClaimType),YearMonth))

[Kushal_Chawda]

Try this

=sum(aggr(Count( Distinct {<Claim_Type={'OP', 'TPPD'}>}Claim_Type),YearMonth,CLAIMNUMBER))

[tresesco]

Hi Avinash,

It is optional. Quotes are required only if any string has space in between. Otherwise, it works with/wihout quotes.

[Anonymous]

try this one :

count({<Claim_Type={'OP','TPPD'}>} DISTINCT CLAIMNUMBER )

Thanks

Sattya

[Anonymous]

hi, not sure why but here is there result i get when use this quotes.

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

why when we use aggr, it will always store the count at end of month year instead beginning?

[Anonymous]

When i use this function, result is zero. and there is a red line indicating error under the curly bracket before the claim type.