Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have attached two excel files
Premium
Claims
From the above two files I want your help to make data model to obtain following reports (pivot Table)
Dimension Facts
Policy No Premium
No of Claims
Paid Amount
When the above information is extracted loss_date(claims) should be between From Date and To_date of the policy.
I shall be much thankful if some one help me to build a data model with my attached files
Can you upload your expected result also in Excel File?
See attached qvw.
Hi Upali,
Please check out the attached app. I think this is what you are looking for.
thanks
JV
See the attached file
Hi Manish
Thanks
This is what I am expeceting
Policy No | Premium | No of Claims | Claim Paid | |||||||
1 | 25,000 | 3 | 210,000 | |||||||
2 | 50,000 | 1 | 25,000 | |||||||
3 | 30,000 | 2 | 95,000 | |||||||
4 | 10,000 | 2 | 140,000 | |||||||
5 | 40,000 | 2 | 150,000 | |||||||
6 | 45,000 | 0 | - | |||||||
7 | 35,000 | 0 | - | |||||||
8 | 60,000 | 0 | - | |||||||
9 | 6,000 | 0 | - | |||||||
10 | 12,000 | 0 | - | |||||||
Note | ||||||||||
1 | C4 | 1/8/2015 | 1/18/2015 | 40,000 | Loss date of these two claims are not within policy Period | |||||
5 | C10 | 11/10/2015 | 11/20/2015 | 40,000 |
Hi Sahrma
Thanks for your reply. Can you list in the pivot table other policies where there no claims as well (all ten policies)
1 | C4 | 1/8/2015 | 1/18/2015 | 40,000 | Loss date of these two claims are not within policy Period | |||||
5 | C10 | 11/10/2015 | 11/20/2015 | 40,000 |
The above two claims should not appear in the pivot since loss date not within policy period
hi upali
PFA
hope this helps
@See the attached file again