Charting changes in monthly values in Crystal 1

[Craig350]

I have a chart I need to create that would show the changes between the monthly values of a field - call it loans. The chart will need to do this on a rolling 13 month basis. Anyone have any pointers to get me started? Thanks!

 

[hilfy]

How is your data set up? Do you know what fields you need to select?

I assume there's a date field in there. To get the start of the month 13 months you'll use a formula that will look something like this:

{@StartDate}
numbervar curmonth := Month(CurrentDate);
numbervar curyear := Year(CurrentDate);
numbervar getmonth;
numbervar getyear;
if curmonth = 1 then
(
  getmonth := 12;
  getyear := year - 2;
)
else
(
  getmonth := curmonth - 1;
  getyear := curyear - 1;
)
Date(getyear, getmonth, 1)

You'll then manually edit the selection formula in the select expert to something like this:

{table.date_field} >= {@StartDate}

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Craig350]

Thanks again, Dell! The calculation to graph the change between each of the 13 mos is where I get stumped. My report is written with a SQL Command using the following where statement:

WHERE
Loan_Master.FILEDATE >= dateadd(mm, datediff(mm, 0, getdate())-13, 0)

Which ensures the report looks over the last 13 mos. But getting at the each monthly change I am trying to graph is what stumps me. How do I write a formula to at each month's successive difference?

Thanks again for your assistance!

 

[hilfy]

Do you have the specific numbers for each month? Instead of using tables in Crystal, you'll probably need to write a command that will aggregate each month's data. Something like:

You don't mention which database you're connecting to. In Oracle, the command would look something like this:

Select
trunc(datefield, 'MM') month_start,
sum(fieldtocount) month_value
from table
group by trunc(datefield, 'MM')

For SQL Server, instead of using "trunc" you would use Convert to convert the date to a year-month format or to help calculate the first of every month.

In Crystal you can then write a formula something like this to get the difference:

{table.month_value} - Previous({table.month_value})

You have to do it this way because I don't think that the Previous function will work with summary formulas.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Craig350]

Dell - this is awesome - thanks. Didn't have to change the select statement, and now, I have only one issue outstanding. My chart is done only in GH 1a which replicates the chart for each LOB (the group). It works fine on the first LOB, and it doesn't calculate a monthly growth for the first month - but only for the first LOB. It does calculate a growth for the first month in all the subsequent LOBs though, and of course the "growth" is the difference between 0 and the first month's value. Why would it do that on the second and remaining groups only? Here are my formulas and chart values:

If {Command.FILEDATE} >= {@Mo Prior 13 BOM}
And {Command.FILEDATE} <= {@Mo Prior 01 EOM}
Then {Command.Exposure_$}

@PCMTMGT: {@PCMTMG1} - Previous({@PCMTMG1})

@PCMTMG1: If {Command.FILEDATE} >= {@Mo Prior 13 BOM}
And {Command.FILEDATE} <= {@Mo Prior 01 EOM}
Then {Command.Exposure_$}

Chart:
On Change of: Command.FILEDATE - A
Show Values: Sum of @PCMTMGT

All months' growth other than the first month growth on the second and remaining LOBs calculate perfectly, so how do I get it to not calculate the first month's growth, or do it correctly? I tried changing the my SQL command date calc to back 14 months, but then it shows all 14 mos on each graph (which I don't want), and it repeats the same error in growth on the first month.

Thanks - you're awesome!

 

[hilfy]

You'll need to make one tweak to the @PCMTMGT formula

if {LOB} = previous({LOB}) then
{@PCMTMG1} - Previous({@PCMTMG1})

This way you'll only calculate starting with the second record after the change of group.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Craig350]

Dell - almost! This provides a better visual, but what it now does with the 2nd and remaining LOBs is to chart the first month's value at zero - which is better than before, but I want to eliminate the first month from the 2nd LOB on and not have it plot zero - doing so gives the false impression the LOB was flat with the prior month, and then it falsely calculates a change from the first month to the second. I tried multiple variations of that tweak you sent but couldn't get any to work! The first LOB is still correct, ie, it shows no value for the first month.

Thanks! I'll owe you one after this!

 

[hilfy]

Easy:

if {LOB} = previous({LOB}) then {@PCMTMG1} - Previous({@PCMTMG1}) else {@PCMTMG1}

A computer only does what you actually told it to do - not what you thought you told it to do.