Rolling Months

[Dukester0122]

I have a report that's called Rolling Month Sales. The user enters the end date of the report then will show a 6-months sales. The last month would be the month he entered when the report ask him the date, example 03/31/03. What's the formula to get the current month(03/31/03) and the 6 previous months. Tks.

 

[chelseatech]

I've done a few of these and have found this technique useful.

The easiest approach is to put a date in the column heading of the 1st of each month, and then format it to Mon-YYYY.

I've also found people prefer the columns to go forwards in time rather than back, so the most recent column is on the right.

heading for current month {@ColTM}
numbervar y:=year(CurrentDate);
numbervar m:=month(CurrentDate);
Date(y,m,1)

Heading for previous month {@col-1}
numbervar y:=year(CurrentDate);
numbervar m:=month(CurrentDate)-1;

if m<1 then (m:=m + 12; y:=y-1);
Date(y,m,1)

Then repeat the previous formula and change the -1 to -2, -3, up to -6

Then the formula for this month's data is
if {table.datefield} >= {@colTM) and
{Table.datefield} <= CurrentDate then {table.value}

The value for last month is
if {table.datefield} >= {@col-1) and
{Table.datefield} < {@colTM} then {table.value}

The value for two months ago is
if {table.datefield} >= {@col-2) and
{Table.datefield} < {@col-1} then {table.value}

Copy the previous formula for the subsequent months and change the dates for each selection.

Lots of formulas and copy and paste help here.

Then just summarise your values for each month.


Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Dukester0122]

Chelseatech,

That worked perfect!!!! Thanks for the help.

 

[ShortyA]

You could try
{yourfield} >= dateserial(year(currentdate),month(currentdate)-6,1)

This will look at 6 months previous to the currentdate.
Alec

 

[rhinok]

FYI, 'rolling months' are cumulative values of all months 'rolled up' into the appropriate month. For example:

1. Last 6 months - would be Nov 2002 to Apr 2003
2. 6 months ending Mar 2003
3. 6 months ending Feb 2003
4. 6 months ending Jan 2003
5. 6 months ending Dec 2002
6. 6 months ending Nov 2002

So for 6 true rolling months, you really want to select data from June 2002 - April 2003 (11 months of data). You could still use Chealseatech's formulas to break out the column values. Just modify the date ranges.

Since your users are only entering in the 'Through Date' value as a parameter, you'll need to derive the 'From Date' in a formula. Hopefully, this formula can be passed to the database. You could try the following record selection criteria:

//Record selection formula
{Orders.Order Date} In
Switch
(
{?Through_Date} <> Date(1900,01,01),//Default Value
Date(Year(DateAdd('m',-10 ,{?Through_Date})),Month(DateAdd('m',-10 ,{?Through_Date})),1) To {?Through_Date},
True,
Date(1900,01,01) To Date(1900,01,01)//Default Date Range in case the Param is left blank
)

In my test report, the record selection passed to SQL Server (based on a parameter of 04/30/2003) was:

SELECT
table.&quot;createdate&quot;
FROM
&quot;datbase&quot;.&quot;dbo&quot;.&quot;table&quot; table
WHERE
table.&quot;createdate&quot; >= {ts '2002-06-01 00:00:00.00'} AND
table.&quot;createdate&quot; <= {ts '2003-04-30 23:59:59.00'}

If you don't want true rolling months, you can still use the formula above as a method of deriving the 'From Date' and passing the value to the database.

 

[Dukester0122]

rhinok,

Here's the formula I used to get the rolling months which worked fine:
IF month({?ReportDate}) = 1 and (Year({table.DATE}) = year({?ReportDate}) - 1)
and Month({table.DATE}) = 12 then {@Amount}
else
if Year({table.DATE}) = year({?ReportDate})
and (Month({table.DATE}) = month({?ReportDate}) -1)
then {@Amount}
else 0

 

[Dukester0122]

rhinok,

you seem to know about accounting then I have a question for you. I did a report for inventory and can't seem to get one column working. The objective is to get the ending inventory of last month then show it as beginning of this month. The report shows the following columns which I'm sure everybody have seen on every accounting book:
Beginning (last months ending)
add purchases
less sales
= Ending

I used this formula to get my positive and negative numbers:
if {table.QTY} >= 0 then {table.QTY} and
if {table.QTY} < 0 then {table.QTY}

Then just added the result to get my ending but I'm having problems getting the beginning. The report has parameter for the date range. Do you have any suggestions?

Thanks.

 

[rhinok]

Dukester0122,

The important thing to note about your Date selection formula is that it will be processed on the client machine (your CE Server), not on the Database Server. Generally speaking, formulas aren't passed to the DB server except in certain circumstances (usually involving boolean logic).

Within Crystal Reports, select Database|Show SQL Query. If you don't see a 'Where' clause then you're going to pull back every record in the database onto the client machine before you evaluate the date formula. Depending on the size and integrity of your DB this might be problematic.

Do you really want to bog down your CE server by processing large amounts of data or do you want to pass the data requirements to your DB server for processing?

 

[rhinok]

Regarding your second question, that formula doesn't really do anything. You could just as easily use the {table.QTY} field since all the formula does is return all values anyway.

Also, what do 'months' mean to your company? Are they calendar months or fiscal months? If they're fiscal, how are they defined? 4-4-5? Are you letting the users enter in any date value in the date range parameters or do you have lists from which they can select values?

In your case, it would probably be better to create lists of date values for the date range parameters. If the user enters in an odd date, how are you to calculate the appropriate values for the prior month?

Since you're basically looking for two months (assuming calendar) worth of data, you could simply use a modified version of the Switch statement posted above. Simply change the number of months subtracted from 10 to 1.

Next, use formulas similar to the ones proposed by Chelseatech for displaying values by month. Sum those values for the previous month and you have your Beginning Qty.

 

[Dukester0122]

rhinok,

We are using calendar months.

I tried your Switch statement but got the &quot;A date is required here.&quot; message on the 4th line....Date(Year(DateAdd....

 

[rhinok]

You must use a Datatype of 'Date' as the basis for your {?Through_Date} parameter.

 

[Dukester0122]

The parameter was assigned as date type.

 

[rhinok]

If you're getting the date error message then the field you're parsing 'Year' and 'Month' values from isn't a date. Is it a datetime field?

Another possibility is that you've dropped a comma or paren somewhere. Please paste in your formula with an indicator that shows where in the formula you're receiving the error message.

 

[Dukester0122]

The field I was parsing is actually a datetime field.

Here's the formula:

{IV30300.DOCDATE} In
Switch
(
{?Through_Date} <> Date(1900,01,01),//Default Value
Date(Year(DateAdd('m',-10 ,|{?Through_Date})),Month(DateAdd('m',-10 ,{?Through_Date})),1) To {?Through_Date},
True,
Date(1900,01,01) To Date(1900,01,01)//Default Date Range in case the Param is left blank
)

The error is where the | sign is which is on the 5 row.

 

[rhinok]

Ok, so Crystal is telling you that {?Through_Date} needs to be a date in order for you to to use it in the dateadd function. You have a couple of different options:[ol][li]Change the {?Through_Date} param to a date instead of datetime[/li][li]Convert the {?Through_Date} param from a datetime to a date using the DateTimeToDate() function[/li][/ol]Since the Switch statement should be used in your record selection formula, there's not really a need to have {?Through_Date} set to datetime. I'd just change the parameter. You may need to convert docdate to a date also (if its a datetime). You can do this under File|Report Options|Convert Date-Time Field.

 

[Dukester0122]

I decided to change the {?Through_Date} param to a date. Set the Convert Date-Time field- To Date. Now the part where I use the DateTimeToDate isn't clear. Do I just change the DateAdd function? I would appreciate if you could give me show me the formula using the DateTimeToDate function. Thanks.

 

[rhinok]

If you've changed the {?Through_Date} to a date and you're converting Date-times to Dates, then you don't need to use the DateTimeToDate function. You're statement should work now.

 

[Dukester0122]

I'm still getting the error when I run the report. Is there another way how to get this?

 

[Dukester0122]

rhinok,

I tried to change the {?Through_Date} param to a discrete value then run the report and it worked fine. But this is not what the report should do. It's suppose to give the option for a start and end date.

 

[rhinok]

Quote: &quot;The user enters the end date of the report then will show a 6-months sales. The last month would be the month he entered when the report ask him the date, example 03/31/03. What's the formula to get the current month(03/31/03) and the 6 previous months.&quot;

The formula does exactly what you asked for. It calculates the 'From Date' based on the 'Through Date' entered via the discreet {?Through_Date} parameter.

I'd recommend against allowing a date range because a manual crosstab (basically what you're creating using Chelseatech's formulas) requires a static number of columns. If you allow a date range parameter, you can't control the size of the range input by the users.