Getting Year to Date and Current Period data

[crystalpro]

Is there a way to get Current Month and Year to Date data on a report when there is a date parameter used with a selection criteria to select transactions between the dates entered in the prompt? This is for Crystal Reports for Peachtree.

 

[synapsevampire]

Year to date always encompasses the current month, so if the dat range entered is for the year to date period, you'll be covered.

If they respond to the prompt with just yesterdays date, as you probably know, you can't get year to date nor the current month.

Assuming that they're entering a date range which encompasses at least the current year to date and current month, than you can limit rows in formulas or Running Totals using the following:

//Year formula
Year({MyTable.MyDateField}) = year(currentdate)

//Month Formula
Year({MyTable.MyDateField}) = year(currentdate)
and
Year({MyTable.MyMonthField}) = Month(currentdate)

The data should already be limited as the result of the parameter, if not, you'd place something in the record selection criteria akin to:

{MyTable.MyDateField} = {?MyDateParm}

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[crystalpro]

So if I ask the user to enter a beginning and ending date range for the report {?Report Date Range}, then I should not use any selection criteria to select when {JrnlHdr.TransactionDate} = {?Report Date Range}?

If there is no selection criteria, then we use the formula you suggested to limit the data on the rows.

//Year formula
Year({MyTable.MyDateField}) = year(currentdate)

//for Peachtree data, this would be:
Year({JrnlHdr.TransactionDate} = year(currentdate)

//Month Formula
Year({MyTable.MyDateField}) = year(currentdate)
and
Year({MyTable.MyMonthField}) = Month(currentdate)

Since there is no Month field in Peachtree, I would need to create one. Should the 2nd part to the formula be:

Month({MyTable.MyMonthField}) = Month(currentdate)

Do you know how I would create a month field?

 

[crystalpro]

So if I ask the user to enter a beginning and ending date range for the report {?Report Date Range}, then I should not use any selection criteria to select when {JrnlHdr.TransactionDate} = {?Report Date Range}?

If there is no selection criteria, then we use the formula you suggested to limit the data on the rows.

//Year formula
Year({MyTable.MyDateField}) = year(currentdate)

//for Peachtree data, this would be:
Year({JrnlHdr.TransactionDate} = year(currentdate)

//Month Formula
Year({JrnlHdr.TransactionDate}) = year(currentdate)
and
Year({MyTable.MyMonthField}) = Month(currentdate)

Since there is no Month field in Peachtree, I would need to create one. Should the 2nd part to the formula be:

Month({MyTable.MyMonthField}) = Month(currentdate)

Do you know how I would create a month field?

 

[dgillz]

I would use the record selction formula you mentioned. I see no need to use a month() and Year() formula to limit the report to the data in question.

For the Current Month and Current Year columsn, try these formulas:

If {DateField} in MonthtoDate then {AmountField} else 0
If {DateField} in YeartoDate then {AmountField} else 0 Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

dgillz formulas will work too, I generally explicitly state the criteria to avoid buglets in date and datetime comparisons in CR canned date functions (as in yeartodate), but they shouldn't effect this.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[crystalpro]

I got 0.00 for MTD and YTD.

Here's the formula I used:
For MTD
If {jrnlrow.rowdate} in MonthtoDate then {jrnlrow.amount} else 0

For YTD
If {jrnlrow.rowdate} in YeartoDate then {jrnlrow.amount} else 0

Is there another formula I can use?

 

[synapsevampire]

Display the dates alongside this formula to verify that you have dates in that month and year.

I just reread all of this, and for some reason this simple task has gotten overly complicated.

And you were right, I had a typo in the formula:

//Year formula
Year({MyTable.MyDateField}) = year(currentdate)

//Month Formula
Year({MyTable.MyDateField}) = year(currentdate)
and
month({MyTable.MyMonthField}) = Month(currentdate)

And you don't need to create a month field in Peachtree, month() is a Crystal function.

The data should already be limited as the result of the parameter, if not, you'd place something in the record selection criteria akin to:

{MyTable.MyDateField} = {?MyDateParm}

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com