Minimum date aggregation

[jrbeaver47]

I'm trying to build a report that will give me an average claim size, and break this out by incurred date.

My data can have multiple Dates of Service on one claim....we would like to treat the earliest date of service on a claim as the incurred date.  For example, here's what my data would look like:

Claim   Date of Service   Amount
1   1/15/2015   $30.00
1   2/15/2015   $35.00
2   1/20/2015   $100.00
3   2/20/2015   $80.00
3   4/1/2015   $20.00


Ideally, I would be displaying this on a crosstab, and it would look like this:

Year        January        February
2015        $82.50          $100.00 

..etc.

I just can't figure out how to assign the minimum date at the claim level. If I use a minimum date function, I just get the minimum date of the entire data set.  Do I have to do a nested query (and if so, how do i do that?) Or is there a more clever way to do this in one query?

[gpollock]

I might not be understanding you correctly, but it looks like you're asking two questions.  For your minimum date of service, on your aggregation functions, look up the "for" clause.  I think you should be able to do min([Date of Service] for Claim).  If that syntax doesn't work, there's something similar you can do.

For the average part, just using the average aggregate function should do the trick.  If not, just create a data item and make it average for month or something.  You might have to use total(amount for claim) as one data item, and then set it to summary aggregate as average.

My advice for doing this is to break your work into steps.  I like to have one data item for each step in the process, to make debugging easier.  Also keep in mind the aggregate function and summary aggregate function.  With any "for" calculation, you're likely going to set both to calculated.  With average amount, you might tell it to calculate at the basic level, and average at summary.