Crystal Reports 2008 - Last Full Quarter and Quarter to Date?

LinInDenver
LinInDenver used Ask the Experts™
on
Hi guys,

Any ideas on how I can add Last Full Quarter and Quarter to Date to my report filters? Below is what I have so far for other date ranges (this is the formula for the start date applied to the report.)

Thanks,
Lin


 switch ({?p_MainDateRange} = 'LFW', minimum(lastfullweek)+1 ,
            {?p_MainDateRange} = 'MTD', minimum(MonthToDate) ,
            {?p_MainDateRange} = 'LFM', minimum(LastFullMonth),
            {?p_MainDateRange} = 'YTD', minimum(YearToDate) ,
            {?p_MainDateRange} = 'LFY', minimum(LastYearYTD) ,
            {?p_MainDateRange} = 'LFQ', ????? ,
            {?p_MainDateRange} = 'QTD', ????? ,
     True, currentdate-1
    );

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
These shoulddo it

//@LFQ Start
Date(
Year(DateAdd('m', -(Month({@Today}) Mod 3 + 3), Today)),
Month(DateAdd('m', -(Month({@Today}) Mod 3 + 3), Today)),
01)

//@LFQ END
DateAdd('m', 4, {@LFQ Start})-1

//@QTD Start
Date(
Year(DateAdd('m', -(Month({@Today}) Mod 3), Today)),
Month(DateAdd('m', -(Month({@Today}) Mod 3), Today)),
01)

//@QTD End
Today

Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
The formulas above are close but don't work when the month is the last month of a quarter

//LFQ_Start
Date(
Year(DateAdd('m', (-(((Month(Today) Mod 3) + 2) mod 3)-3), Today)),
Month(DateAdd('m', (-(((Month(Today) Mod 3) + 2) mod 3)-3), Today)),
01)

//QTD_Start
Date(
Year( Today),
Month(DateAdd('m', -(Month(Today) Mod 3 + 2) mod 3, Today)),
01)

mlmcc

Author

Commented:
Thanks guys!

Your are definitely more eloquent than what I came up with!
//LFQ_BEG
date(switch (currentdate in Calendar1stQtr, dateadd("yyyy", -1, minimum(Calendar4thQtr)),
        currentdate in Calendar2ndQtr, minimum(Calendar1stQtr),
        currentdate in Calendar3rdQtr, minimum(Calendar2ndQtr),
        currentdate in Calendar4thQtr, minimum(Calendar3rdQtr),
        true, currentdate));

//LFQ END
date(switch (currentdate in Calendar1stQtr, dateadd("yyyy", -1, maximum(Calendar4thQtr)),
        currentdate in Calendar2ndQtr, maximum(Calendar1stQtr),
        currentdate in Calendar3rdQtr, maximum(Calendar2ndQtr),
        currentdate in Calendar4thQtr, maximum(Calendar3rdQtr),
        true, currentdate));

//QTD_BEG
(switch (currentdate in Calendar1stQtr, minimum(Calendar1stQtr),
        currentdate in Calendar2ndQtr, minimum(Calendar2ndQtr),
        currentdate in Calendar3rdQtr, minimum(Calendar3rdQtr),
        currentdate in Calendar4thQtr, minimum(Calendar4thQtr),
        true, currentdate));

//QTD_END
(switch (currentdate in Calendar1stQtr, maximum(Calendar1stQtr),
        currentdate in Calendar2ndQtr, maximum(Calendar2ndQtr),
        currentdate in Calendar3rdQtr, maximum(Calendar3rdQtr),
        currentdate in Calendar4thQtr, maximum(Calendar4thQtr),
        true, currentdate));


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial