Identify all Quarters associated with a Given Date Range

[Former Member]

Hi,

I am trying to create a Crystal Report that shows all Quarters data for a give date range. For E.g. the user defined date range is 02/20/2013 - 01/15/2014, the report should include the data for Q1-2013 till Q1-2014. Is there a simpler way to achieve this functionality.

I will really appreciate your help/guidance on this.

Regards,

acb

[abhilash_kumar · Accepted solution]

Hi Chandra,

Do you have a Date Range prompt or two separate prompts for Start Date and End Date? If it's a Date Range prompt, use this as the Record Selection Formula:

datevar start := Minimum({?Date_Range_Prompt});

datevar end := Maximum({?Date_Range_Prompt});

{Date_Field_in_Database} >=

(

     If start in Calendar1stQtr then

         Minimum(Calendar1stQtr)

     else if start in Calendar2ndQtr then

         Minimum(Calendar2ndQtr)

     else if start in Calendar3rdQtr then

         Minimum(Calendar3rdQtr)

     else if start in Calendar4thQtr then

         Minimum(Calendar4thQtr)

)

AND

{Date_Field_in_Database} <=

(

     If end in Calendar1stQtr then

         Maximum(Calendar1stQtr)

     else if end in Calendar2ndQtr then

         Maximum(Calendar2ndQtr)

     else if end in Calendar3rdQtr then

         Maximum(Calendar3rdQtr)

     else if end in Calendar4thQtr then

         Maximum(Calendar4thQtr)

)

-Abhilash

[Former Member · Accepted solution]

Thanks Abhilash, it worked like wonder.

~acb.