LAST MONTH YTD And Current Month YTD Problem 1

[Sombo]

Hi,
My Company's Fiscal year starts from April 1st through March 31st,with the following Fiscal periods: 3-31-03
4-26-03
5-24-03
6-28-03
7-26-03
8-23-03
9-27-03
10-25-03
11-22-03
12-27-03

I am required to write a report that calculates Prior Month YTD (PM-YTD) and Current Month YTD (CM-YTD)
I have a Date Range parameter, - @startDate and @EndDate ( eg. 3/31/2003 - 6/28/2003)
The above Date range will give me my CM-YTD.
I need help with my PM-YTD. i.e from 3/31/2003 - 5/24/2003. If I do (@startDate -1 ), I get the wrong amounts because of the above Fiscal Period format, that will obviously change from year to year?
Any help will be greatly appreciated.

This is how my final summary should look like

Period From 3/31/03 to 6/28/03
------------------------------
PM-YTD CM-YTD CM(JUNE)
------ ------ -------
5000 7000 2000

 

[lbass]

Something like this should work for {@PM-YTD}:

if month({?Enddate}) in [3,6,9,12] then
if {table.date} in {?StartDate} to dateadd("ww",-5,{?Enddate}) then {table.amount} else
if {table.date} in {?StartDate} to dateadd("ww",-4,{?Enddate}) then {table.amount} else 0

This assumes that {?StartDate} and {?EndDate} are date type parameters.

-LB

 

[IdoMillet]

LB,

You may need to subtract one day from {?StartDate} to ensure it is not included in PM-YTD.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

Ido,

I'm guessing you are suggesting this because you noticed that Sombo was proposing looking at data from 3/31 to 6/28, and presumably, might consider the next period to be 6/28 to 9/27, in which case data from 6/28 would be represented twice. But, my suggested formula only refers to {?StartDate}, and it seems to me the formula should not be correcting for user error, but instead that the user should choose to use 6/29 for the start date, rather than 6/28. The parameter list for start date could be designed to always reflect one day later than the previous end date, rather than using the same date. Or am I missing your point?

-LB

 

[synapsevampire]

An interestinmg point, LB.

I didn't respond for a more global reason that they didn't supply a years worth of fiscal periods (March to Dec - hence your formula may prove incorrect), they stated that they might change (whatever that means) and I would think that the prompt might be better if it was a text period choice, such as Fiscal Month 1, 2, etc., Fiscal Quarter 1, 2, etc.

I think that the idea of basing it on a user prompted date is a mistake for set periods (though you migh talso include a prompt for dates not in known periods).

An example is to create a start and end date formulas, here a start date example:

////////////////////////////////////////////////////////////////////////////////////
// Starting date of the date range to be used against the column: {LOCALTIME}
// Constructed specifically in this manner to allow for pass through SQL
// Version 2
// Kai Molvig - 5/2/2002
////////////////////////////////////////////////////////////////////////////////////

// If Yesterday
If {?Predefined Date Range} = "Yesterday" Then
DateTime(Year(CurrentDate-1),Month(CurrentDate-1),Day(CurrentDate-1),0,0,0)

// If Last Week
Else
If {?Predefined Date Range} = "Last Week" Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),0,0,0)-DayOfWeek(CurrentDate)-6

// If Last Month
Else
If {?Predefined Date Range} = "Last Month" Then
If Month (CurrentDate) = 1 Then
DateTime((Year(CurrentDate)-1),12,1,0,0,0)
Else
DateTime(Year(CurrentDate),Month(CurrentDate)-1,1,0,0,0)

// If Last Quarter
Else
If {?Predefined Date Range} = "Last Quarter" Then
If (Month(CurrentDate) < 4) Then
DateTime(Year(CurrentDate)-1,10,1,0,0,0)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)+2)/3)-5,1,0,0,0)

etc...

Altering the dates to suit the fiscal periods. This would mean hardcoding them based on some

Then referenceing these formulas in the record selection.

-k

 

[lbass]

SV -
I think I, not Sombo, added the idea of parameters, but you are probably right that it would make more sense to use a defined start date and end date for record selection.

It appears that Sombo is defining each quarter as 13 weeks in length (4 + 4 + 5 weeks), and that the first quarter starts in the week which contains April 1. For all periods defined by Sombo except that starting 3/31, the 4 or 5-week periods start on a Saturday, and therefore it seems to me that week one should start on 3/29, not 3/31.

Using parameters for quarter and calendar year, a formula for {@startdate} might be:

datevar startdate;
datevar qtrstartdate;

startdate := Date({?calyr},04,01) - Dayofweek(Date({?calyr},04,01));
if {?Qtr} = 1 then
qtrstartdate := startdate else
if {?Qtr} = 2 then
qtrstartdate := date(dateadd(&quot;ww&quot;,13,startdate)) else
if {?Qtr} = 3 then
qtrstartdate := date(dateadd(&quot;ww&quot;,26,startdate)) else
if {?Qtr} = 4 then
qtrstartdate := date(dateadd(&quot;ww&quot;,39,startdate)) ;
qtrstartdate;

A formula for {@enddate} to define the end of the quarter might be:

datevar startdate;
datevar enddate;

startdate := Date({?calyr},04,01) - Dayofweek(Date({?calyr},04,01));
if {?Qtr} = 1 then
enddate := date(dateadd(&quot;ww&quot;,13,startdate)-1) else
if {?Qtr} = 2 then
enddate := date(dateadd(&quot;ww&quot;,26,startdate)-1)else
if {?Qtr} = 3 then
enddate := date(dateadd(&quot;ww&quot;,39,startdate)-1) else
if {?Qtr} = 4 then
enddate := date(dateadd(&quot;ww&quot;,52,startdate)-1) ;
enddate;

The record selection statement would be:

{table.date} >= {@startdate} and
{table.date} <= {@enddate)

...although maybe there's a better way since this wouldn't make it into the SQL statement...

Then for previous &quot;month&quot; period to date:

if month({?Enddate}) in [3,6,9,12] then
if {table.date} in {@StartDate} to date(dateadd(&quot;ww&quot;,-6,{@Enddate})) then {table.amount} else
if {table.date} in {@StartDate} to date(dateadd(&quot;ww&quot;,-5,{@Enddate})) then {table.amount} else 0

Since the &quot;in&quot; statement includes the ending week, I think the end point has to be one week earlier than I indicated in my original post.

-LB