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

[upaliwije]

Thanks

But Two Claims should be excluded  C4 and C10 They are not within period

[upaliwije]

Thanks

These two claim should be excluded. Pls help

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

Data Model

[jvitantonio]

Upali, I think the problem is in your data source. In the premium file for Policy No = 1, you have a

Date From 01/01/2014 and a Date To 31/12/2013. Date To should be earlier thank Date From.

JV

[#5]

great Observation

[upaliwije]

It is true there is a mistake. Even if the the mistake is corrected my issue not corrected

[jvitantonio · Accepted solution]

Hi Upali,

Check out the attached file.

JV

[MK_QSL]

I am almost there.... But the result only showing when you select the Policy Number.

Can someone correct it. would be thankful.

[upaliwije]

Thanks all for your time taken