Calculating previous month

[wiplash]

I need to calculate the previos period (previous month) based on the period parameter the user enters, and imagine there's a much easier way than the all of the formulas I currently think necessary. Using CR11.5. The period is listed in the database as a 6 digit field (yyyymm) and not an actual date.

For example, the user might request 201407, therefore I need to show 201407 records AND 201406 records.

Thank you and any advice appreciated!

 

[Charliy]

Here's one way to do this:
First you need to convert the input period to numeric year and month.
{@select year} val(left({?date parameter},4))
{@select month} val(right({?date parameter},2))

Now create an end date:
{@edate} date({@select year},{@select month},1)

And a begin date that is one month earlier:
{@bdate} dateadd('m',{@edate},-1)

Now we can set up a range, we can still use the periods, but turn them into numbers.
First convert the Begin Date:
{@start} val(totext(@bdate},"yyyyMM"))

And the End Date:
{@stop} val({?date parameter})

And your Select formula would include:
val(date field} in {@start} to {@stop}

 

[wiplash]

Thanks Charliy, but I'm getting an error on the formula to calculate the begin date and so far haven't determined the fix (or cause).

Specifically, the formula .......... @bdate} dateadd('m',{@edate},-1)

The @edate part says "a number is required here". I am probably doing something wrong, but would appreciate your taking a look.

Thanks again!

 

[BettyJ]

Try this
@bdate
dateadd('m',-1,{@edate})
Also, what is the format of the date parameter entered?

 

[wiplash]

That worked perfectly, thanks much! By the way BettyJ, the parameter actually shows a list of possible periods as read in the SQL table, field defined as character. Can't explain why, but that's the way it was defined years ago.
Thanks once again!!!

 

[Charliy]

Sorry I couldn't get back to you - I was in a class all week. Thanks Betty for picking it up and fixing my typo.

 

[wiplash]

Hey it's all good and thanks again to both of you!