Current Month, Year Ago Month 3

[BradW]

Thanks in advance for your help.

I am working on a report that will total up items from the preceding month, such as a total for May. I want to then compare that to the total for May 2003 and create a formula that would indicate the increase/decrease from the prior period in percentage of change. I can do the % change formula, but I'm not sure how to get the current month/year ago month stuff to work just right on a report.

Your help is appreciated!

 

[synapsevampire]

Since you didn't state how this was to be used, here's an overview:

To obtain data for last mmonth and a year ago month, change the record selection formula to read something like:

(
{table.date} in lastfullmonth
)
or
(
{table.date} >= dateadd("y",-1,minimum(lastfullmonth)
and
{table.date} <= dateadd("y",-1,maximum(lastfullmonth)
)

Now you have the right data.

Group by the year and you have the 2 different sets of data.

As for displaying a percentage difference, you might create 2 running totals and compare the 2 at the end. Each would have a year qualifier in the Evaluate->Use a formula such as:

year({table.field}) = year(minimum(lastfullmonth))

for last year:

year({table.field}) = year(minimum(lastfullmonth)))-1

Then a formula referencing the 2 Running Totals might be used for percentage of change:

(#RT1/#RT2)*100

In future posts, try posting technical information rather than text if you want more tailored help.

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[rosemaryl]

Dear BradW,

Well let's assume that you allow your users to choose the date range and you always want to compare the date range they selected with the same date range a year ago.

Her is how I would do it:

First, create two parameters, type date.

FDATE, FTDATE

Now, your record selection criteria: Report/Edit Record Selection Criteria/Record would be (using my fields for example):

{Incident.Open Date & Time} in
 
({?FDATE} to {?FTDATE})
or
{Incident.Open Date & Time} in
(cdate(year({?FDATE})-1,month({?FDATE}), day({?FDATE}))
to
 cdate(year({?FTDATE})-1,month({?FTDATE}), day({?FTDATE}))
)
//End of Formula

Now that selects the records for you and it passes the sql to the database which is a nice start. So only the records with the date in the range or in the range a year ago appear.

Now, the rest depends on what you want the report to look like. I would hate to go down the rosy path on a solution only to find that you needed the data a different way.

Give me an example of the output you want, it would also be helpful to know what database, (driver), and version of Crystal.

Regards,

ro



Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[BradW]

Thanks to both, they were quite helpful.

Rosemary, What I would like it to look like is:

Category 1 Current MO Value Year Ago MO Value Change
Category 2 Current MO Value Year Ago MO Value Change
(and so on)

Crystal Version is 8.5 (but I can use 9 or 10 if there is a benefit), database is an Oracle database using the native Oracle drivers.

Thanks again for your help.

 

[rosemaryl]

Dear Brad,

Ok, it would be helpful to know if you are going with SV's solution to pull the last month and last month for prior year or by using parameters.

Regardless, you can use the following technique, just reference the date range correctly. I am going to show the example with parameters.

You will Group by whatever field is Category

You will need to create 2 Formula Fields for the detail section and then one formula for the Group Footer

//Formula 1 CurrentMo Value

If {Table.YourDateTimeField} in ({?FDATE} to {?FTDATE})
then {Table.ValueField} else 0
//end

//Formula 2 MonthValueYrAgo
If {Table.YourDateTimeField} in (cdate(year({?FDATE})-1,month({?FDATE}), day({?FDATE}))
to
cdate(year({?FTDATE})-1,month({?FTDATE}), day({?FTDATE}))
then {Table.ValueField} else 0

Now, what this does is in the details you will see a value in one column or the other, never both.

Now, you would click on the formula1 in the detail section and click the Summary Icon, Sum, Insert Totals For All Groups, Insert Grand Total.

Do the same for formula2.

Now, suppress the details.

Move the group Name from the Header to the Footer for the group.

Suppress the group header.

You should see:

Category 1 Current MO Value Year Ago MO Value Category 2 Current MO Value Year Ago MO Value

Now, the only thing left to do is to create a formula to show the percent of change for each category, which you said you already know how to do.

Place that formula in the desired position in the group footer ... and you could also create another formula to show the Grand Total Percent of Change.

Regards,

ro




Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[BradW]

Thank you so much, Rosemary, this was extremely helpful!