Data Model

[upaliwije]

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

[MK_QSL]

Can you upload your expected result also in Excel File?

[Gysbert_Wassenaar]

See attached qvw.

[jvitantonio]

Hi Upali,

Please check out the attached app. I think this is what you are looking for.

thanks

JV

[er_mohit]

See the attached file

[upaliwije]

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

[upaliwije]

Hi Sahrma

Thanks for your reply. Can you list in the pivot table other policies where there no claims as well (all ten policies)

[upaliwije]

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

[#9]

hi upali

PFA

hope this helps

[er_mohit]

@See the attached file again