Skip to Content
author's profile photo Former Member
Former Member

Last day of last quarter - Crystal 2008

Good morning,

I have a crystal report with a record selection that brings back records from 1 January 2012 to the end of the last full quarter. The record selection includes the following:

{table.DateTimeOfIncident} >= date(2012,01,01) and

(if currentdate in Calendar1stQtr then {table.DateTimeOfIncident} < Calendar1stQtr

else if currentdate in Calendar2ndQtr then {table.DateTimeOfIncident} < Calendar2ndQtr

else if currentdate in Calendar3rdQtr then {table.DateTimeOfIncident} < Calendar3rdQtr

else {table.DateTimeOfIncident} < Calendar4thQtr)

This is working correctly.

I also have a formula to put in the header to describe the time period (i.e. This report contains records from 01/01/2012 to xx/xx/xxxx

How do I get the xx/xx/xxxx to represent the last day of the last quarter?

Kind regards,

Anna.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on May 29, 2013 at 06:14 AM

    Hi Anna,

    I would like to propose a change to the Record Selection formula as well. The Calendar1stQtr and other quarter functions are date ranges. So when you compare it with other date fields using the '<' and '>' operators you should actually use the Minimum or Maximum functions to extract the 1st day or the last day of that quarter. So, the formula will be:

    {table.DateTimeOfIncident} >= date(2012,01,01) and

    (if currentdate in Calendar1stQtr then {table.DateTimeOfIncident} < Maximum(Calendar1stQtr)

    else if currentdate in Calendar2ndQtr then {table.DateTimeOfIncident} < Maximum(Calendar2ndQtr)

    else if currentdate in Calendar3rdQtr then {table.DateTimeOfIncident} < Maximum(Calendar3rdQtr)

    else {table.DateTimeOfIncident} < Maximum(Calendar4thQtr))

    For the formula on the header, you can use this code:

    datevar dd := if Maximum({table.DateTimeOfIncident}) in Calendar1stQtr then Maximum(Calendar1stQtr)

    else if Maximum({table.DateTimeOfIncident}) in Calendar1stQtr then Maximum(Calendar1stQtr)

    else if Maximum({table.DateTimeOfIncident}) in Calendar2ndQtr then Maximum(Calendar2ndQtr)

    else if Maximum({table.DateTimeOfIncident}) in Calendar3rdQtr then Maximum(Calendar3rdQtr)

    else Maximum(Calendar4thQtr);

    "The report contains records from" &cdate(2012,01,01) &" to "& dd;

    Hope this helps!

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.