cancel
Showing results for 
Search instead for 
Did you mean: 

Previous Year to Date formula

Former Member
0 Kudos

I am working on a report that returns records for a certain date range. I have a Parameter named pEndDate. I put together the following formula.

Date(Year({@pDateEnd}-1),Month({@pDateEnd}),Day({@pDateEnd}))

When I give the report a parameter value of "2010-03-21"

The formula reads 3/21/2010

I want it to read 3/21/2009 (the current year minus one).

How do I do that?

Thanks in advance.

Accepted Solutions (1)

Accepted Solutions (1)

former_member5472
Active Contributor
0 Kudos

Hi,

What is the data type of the parameter date filed ; If it is string then you need to segregate the year and the do the minus 1 operation.

Thanks

Pratyush

Former Member
0 Kudos

The parameter is a Number field. I will try to change it to a string and see if that makes any difference.

Former Member
0 Kudos

Try this...


DateAdd("yyyy", -1, @pDateEnd)

HTH,

Jason

Former Member
0 Kudos

Jason,

That is exactly what I needed. I wasn't aware of the DateAdd function. Thank you very much.

I made the adjustment to the formula, but that has exposed an issue with a running total that I am working on.

{ReportStatic.rpsActualSaleDate} > date(1/1/({?pYear}-1))

and {ReportStatic.rpsActualSaleDate} < {@fLastYearToDate}

and {ReportStatic.rpsSaleStatus} = 1

In the first line of the forula, is the Date() function the proper function to be using?

I am trying to report on ticket sales from the beginning of last year, through the current last year to date.

Former Member
0 Kudos

I would do it like this...


{ReportStatic.rpsActualSaleDate} >= DateAdd("yyyy", DateDiff("yyyy", #1/1/1900#, @pDateEnd)-1, #1/1/1900#) 
AND {ReportStatic.rpsActualSaleDate} <= @pDateEnd
AND {ReportStatic.rpsSaleStatus} = 1

So if @pDateEnd = 3/26/2010... That will give you a range from 1/1/2009 - 3/26/2010.

I think that's what you were asking for

Former Member
0 Kudos

Jason,

That is close to what I'm asking for. I'm looking for a date range in "last year".

If @pEndDate is 3/26/2010 I need 1/1/2009 to 3/26/2009

Former Member
0 Kudos

In that case...


{ReportStatic.rpsActualSaleDate} >= DateAdd("yyyy", DateDiff("yyyy", #1/1/1900#, @pDateEnd)-1, #1/1/1900#) 
AND {ReportStatic.rpsActualSaleDate} <= DateAdd("yyyy", -1, @pDateEnd)
AND {ReportStatic.rpsSaleStatus} = 1

Former Member
0 Kudos

I'm sure you already know this, but you are the man! Thank you very much for your help.

Former Member
0 Kudos

Haha. Thanks for the compliment & no problem. Glad you got it to work.

Answers (0)