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?
I got this results.
Year | 2012 | 2013 | 2013 | 2014 | 2015 |
Month | Dec | Jan | Apr | Jun | Jun |
No of Claims | 1 | 1 | 1 | 1 | 1 |
Thanks for the clarification sir
Hi
try this
=Count(Distinct({<CLAIMTYPE={'OD','TPPPD'}>}CLAIMNUMBER)
It returns 0.
=Count(Distinct({<CLAIMTYPE={'OD','TPPD'}>}CLAIMNUMBER)
Month | Dec | Jan | Apr | Jun | Jun |
No of Claims | 0 | 0 | 0 | 0 | 0 |
How do you get 201202 = 1 in your Pivot when there is no data for that period?
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.
Month | Count(DISTINCT{<CLAIM_TYPE={'OD','TPPD'}>}CLAIMNUMBER) |
---|---|
1 | |
01 | 1 |
03 | 1 |
04 | 1 |
06 | 1 |
12 | 1 |
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?
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.
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.