Setting up and comapring point in time data

[ejgallagher]

Hi all,

Here is my situation. I have claim runs that I am loading into my access database. I want to be able to compare monthly loads by the claim number and see how claims grow in a month by month basis.

I have a table with all of the claims information - how is it possible compare data over time?

Thanks,

Ed

 

[Andrew Fergus]

Hi, this can be done but it depends on the data in your claims table. Does your table of claims include a date field, such as the date the item was loaded? Or does your table have a field such as the claim run number? Or is there another sort of date such as the date the claim was opened? If none of the above, I recommend you advise the structure of the main table with all of the variable names so that someone here may help.
Andrew.

 

[ejgallagher]

I have the following as part of my data:

Claim Number - Unique Identifier
Claim Occurrence Date
Claim Closed date - if applicable
Claim Valuation date - when I pulled the data


The only item that will change in this set would be the valuation date. What I would want to be able to do is compare values of paid amounts between valuation dates - showing growth spikes and averages.


Thanks,

Ed

 

[Norie]

Well you can extract the month from a date field like this:

Month:Month([DateField])

and the year similarly

Year:Year([DateField])

You could use these expressions in a query to group by month/year and count/sum claims.