quarterly data 1

[patty1]

I have the following formula in Report Select Expert to automatically capture quarterly results without a date prompt (excuse month format, that's how it is on the database for whatever reason)

if month(CurrentDate) in [1.00 to 3.00] then month({Transaction_History.Post_Date}) in [10.00 to 12.00 ] else
if month(CurrentDate) in [4.00 to 6.00] then month({Transaction_History.Post_Date}) in [1.00 to 3.00 ] else
if month(CurrentDate) in [7.00 to 9.00] then month({Transaction_History.Post_Date}) in [4.00 to 6.00 ] else
if month(CurrentDate) in [10.00 to 12.00] then month({Transaction_History.Post_Date}) in [7.00 to 9.00 ]

The formula works fine except that it gives me every quarter for every year. How can I insert current year in the formula except when month in [1 to 3] in which I will need the previous year? Should I do a minus 3 months kind of formula?

 

[rosemaryl]

Dear Patty1,

The following formula works, I just tested. However, it is not getting passed down to the server (no where clause) so I will try to fine tune, but in the meantime this will pull the last quarter and accounts for prior year.

//begin formula
(if month(CurrentDate) in [1.00 to 3.00] then month({Transaction_History.Post_Date}) in [10.00 to 12.00 ] else
if month(CurrentDate) in [4.00 to 6.00] then month({Transaction_History.Post_Date}) in [1.00 to 3.00 ] else
if month(CurrentDate) in [7.00 to 9.00] then month({Transaction_History.Post_Date}) in [4.00 to 6.00 ] else
if month(CurrentDate) in [10.00 to 12.00] then month({Transaction_History.Post_Date}) in [7.00 to 9.00 ] ) and
if month(CurrentDate) in [1.00 to 3.00]

then Year({Transaction_History.Post_Date}) = Year(CurrentDate)- 1

else
Year({Transaction_History.Post_Date}) = Year(CurrentDate)
//end formula

Hope this helps.

Synapsevampire is very good at getting this stuff to pass. Do you have a suggestion Kai?

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[patty1]

ro - this is great, it works! I wonder if you can recomend any books for me. I bought The complete reference CR7 by George Peck, but it does not seem to go into anything like this. Thank you again for your help!

 

[synapsevampire]

I've had poor results with using Crystal's MONTH() against a database field and getting it passed through, if you want to get it to pass, create a SQL Expression to return the month from the date and reference that in your record selection criteria.

If you're using SQL Server, create a SQL Expression something like:

(I'll call it %MonthDate)
month({Transaction_History.Post_Date})

Now reference %MonthDate in your record selection criteria, I believe that the SQL will now be properly constructed and passed.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Whooops, if you're using CR 7 then my pass through won't work.

Ro: This is how I resolve month pass through, for some reason Crystal often chokes on the SQL when using MONTH() against a database field...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[rosemaryl]

Dear Patty,

You are very welcome. I don't think you will find any book that goes deeply into logic. The books teach you the basics and you have to extrapolate from there.

I am working on creating a better formula right now.

I will post when finished! Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.