Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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
Hi
tresesco sir, I think quotes need to be add rite {'OP','TPPD'}
try like this:
Count( Distinct {<Claim_Type={'OP','TPPD'}>}Claim_Type)
use this expression
count( distinct aggr( {<ClaimType = {'OP','TPPD'}>}ClaimType),YearMonth))
Try this
=sum(aggr(Count( Distinct {<Claim_Type={'OP', 'TPPD'}>}Claim_Type),YearMonth,CLAIMNUMBER))
Hi Avinash,
It is optional. Quotes are required only if any string has space in between. Otherwise, it works with/wihout quotes.
try this one :
count({<Claim_Type={'OP','TPPD'}>} DISTINCT CLAIMNUMBER )
Thanks
Sattya
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?
When i use this function, result is zero. and there is a red line indicating error under the curly bracket before the claim type.