Year to Date Calculation with two date fields.

[mrpatak]

Hi all,
Little bit new to Crystal (using XI), so bear with me. I have a Access database that includes two different dates for every data piece. Each one has a date for the month the data is collected, and the month that the data is reported (eg. a month could be collected for November, but not reported until January).
The report generated graphs many different sets of data, sorted on which goal they pertain to.
I need a way to summarize this data for a year to date calculation without specifically designating a year. Ideally it would look at the most recent month (collection date) and give the year-to-date for that year. They can be either an average of the data set, a sum of the data set, or an average of the percentage of the data set.
I've tried setting up something to this effect:

{@Datayear}:
DatePart("yyyy",{table.data_collected_month})

Then using that as a selection part of a summary field:

{#YTDVolume}:
Field to Summarize: table.numberofcases
Type of summary: Sum
Use a Formula:
{table.data_collected_month} >=Date ({@Datayear},01,01)
On change of group: table.goal#

But this doesn't seem to work.
Any help would be appreciated!

 

[CoSpringsGuy]

not sure I completely understand but why not group on {table.data_collected_month} and in options select year then a summary (SUM) on table.numberofcases

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[mrpatak]

not sure I completely understand but why not group on {table.data_collected_month} and in options select year then a summary (SUM) on table.numberofcases

I'm not really sure what you mean here. The way the report is currently grouped is so that each goal has a separate section of the report with a chart and other information displaying the past 12 months.
Also I'm trying to avoid selecting the year entirely, as it would have to be selected individually for every goal (I think) based on which ones are in which year.

 

[CoSpringsGuy]

ok so I am trying to picture your report ...

You have a report grouped by goal. (How is goal computed? Is it a field in the database?)

Then under each goal you have some data that is charted.
And now you are looking to compute the numberofcases but only those number of cases for all months in the year for the newest month?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[mrpatak]

You have a report grouped by goal. (How is goal computed? Is it a field in the database?)

Yes, the goals are fields in one table of the database.

Then under each goal you have some data that is charted.
And now you are looking to compute the numberofcases but only those number of cases for all months in the year for the newest month?

Exactly. Sorry If I was a bit wordy getting to the point.

 

[CoSpringsGuy]

Im sure there are many ways to skin this cat .. try this
create this formula and place in details and in the group footer

numbervar a;
if {yourdb.goal} <> previous({yourdb.goal}) then a := 0;
if year({table.data_collected_month}) = year(Maximum ({table.data_collected_month}, {yourdb.goal}))
then a := a +1

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

whups

numbervar a;
if {yourdb.goal} <> previous({yourdb.goal}) then a := 0;
if year({table.data_collected_month}) = year(Maximum ({table.data_collected_month}, {yourdb.goal}))
then a := a +table.numberofcases

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[mrpatak]

If I'm following idea behind the formula correctly, it seems like it should be fine for what I would need, but it looks like it's adding in the final month twice. For example, the YTD should be 11, with the last month being 2, and it's coming out as 13.

 

[CoSpringsGuy]

my formula is very flawed and I should have thought it through a little better.. my apologies.. I can provide a better solution later but have to be away from computer for a bit at the moment ...



_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[mrpatak]

Take your time, I appreciate the help!

 

[mrpatak]

Just got it working!
Using the same formula field,

{#YTDVolume}:
Field to Summarize: table.numberofcases
Type of summary: Sum
Use a Formula:
{table.data_collected_month} >=Date ({@Datayear},01,01)
On change of group: table.goal#

Except for the "Use a Formula:" field,

DateDiff("yyyy",{table.data_collected_month},(Date (Maximum ({table.data_collected_month}, {table.goal_id})))) < 1

A little bit... bulky, but working. Got the idea from playing around with that maximum function you included.
Thanks for the help!