LastFullQuarter

[ChuckVanT]

Did Crystal Decisions see fit to include "LastFullQuarter" in CR9?

I'm developing a report that is going to be migrated to CR9, in the very near future. I need to do the calculations to sum stuff for the last full quarter.

If CR9 has the LastFullQuarter function, I'll develop it after we migrate.

Thanks for all help!

Chuck

 

[synapsevampire]

I use a Start and End date formulas to do this, and reference them in the Record selection formula. This example has multiple periods, inclusive using a picklist to select the range of interest, and allowing for a custom date range which is entered in another parameter:

////////////////////////////////////////////////////////////////////////////////////
// Starting date of the date range to be used against the column: {LOCALTIME}
// Constructed specifically in this manner to allow for pass through SQL
// Version 2
// Kai Molvig - 5/2/2002
////////////////////////////////////////////////////////////////////////////////////

// If Yesterday
If {?Predefined Date Range} = "Yesterday" Then
DateTime(Year(CurrentDate-1),Month(CurrentDate-1),Day(CurrentDate-1),0,0,0)

// If Last Week
Else
If {?Predefined Date Range} = "Last Week" Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),0,0,0)-DayOfWeek(CurrentDate)-6

// If Last Month
Else
If {?Predefined Date Range} = "Last Month" Then
If Month (CurrentDate) = 1 Then
DateTime((Year(CurrentDate)-1),12,1,0,0,0)
Else
DateTime(Year(CurrentDate),Month(CurrentDate)-1,1,0,0,0)

// If Last Quarter
Else
If {?Predefined Date Range} = "Last Quarter" Then
If (Month(CurrentDate) < 4) Then
DateTime(Year(CurrentDate)-1,10,1,0,0,0)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)+2)/3)-5,1,0,0,0)

// If Last Year
Else
If {?Predefined Date Range} = &quot;Last Year&quot; Then
DateTime(Year(CurrentDate)-1,1,1,0,0,0)

// If Current Week
Else
If {?Predefined Date Range} = &quot;Current Week&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),0,0,0)-DayOfWeek(CurrentDate)+1

// If Current Month
Else
If {?Predefined Date Range} = &quot;Current Month&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),1,0,0,0)

// If Current Quarter
Else
If {?Predefined Date Range} = &quot;Current Quarter&quot; Then
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)-1)/3)+1,1,0,0,0)

// If Current Year
Else
If {?Predefined Date Range} = &quot;Current Year&quot; Then
DateTime(Year(CurrentDate),1,1,0,0,0)

// If Today
Else
If {?Predefined Date Range} = &quot;Today&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),0,0,0)

// If Custom Time Period
Else
If {?Predefined Date Range} = &quot;Custom Date Range&quot; Then
Minimum({?Custom Date Range})

// This code should never be invoked
Else
CurrentDate+1

////////////////////////////////////////////////////////////////////////////////////
// Ending date of the date range to be used against the column: {LOCALTIME}
// Constructed specifically in this manner to allow for pass through SQL
// Version 2
// Kai Molvig - 5/2/2002
////////////////////////////////////////////////////////////////////////////////////

// If Yesterday
If {?Predefined Date Range} = &quot;Yesterday&quot; Then
DateTime(Year(CurrentDate-1),Month(CurrentDate-1),Day(CurrentDate-1),23,59,59)

// If Last Week
Else
If {?Predefined Date Range} = &quot;Last Week&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),23,59,59)-DayOfWeek(CurrentDate)

// If Last Month
Else
If {?Predefined Date Range} = &quot;Last Month&quot; Then
If Month (CurrentDate) = 1 Then
DateTime(Year(CurrentDate)-1,12,1,23,59,59)
Else
DateTime(Year(CurrentDate),Month(CurrentDate),1,23,59,59)-1

// If Last Quarter
Else
If {?Predefined Date Range} = &quot;Last Quarter&quot; Then
If Month (CurrentDate) < 4 Then
DateTime(Year(CurrentDate)-1,12,1,23,59,59)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)-1)/3)+1,1,23,59,59)-1

// If Last Year
Else
If {?Predefined Date Range} = &quot;Last Year&quot; Then
DateTime(Year(CurrentDate)-1,12,31,23,59,59)

// If Current Week
Else
If {?Predefined Date Range} = &quot;Current Week&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),23,59,59)-DayOfWeek(CurrentDate)+7

// If Current Month
Else
If {?Predefined Date Range} = &quot;Current Month&quot; Then
If Month (CurrentDate) = 12 then
DateTime(Year(CurrentDate),12,31,23,59,59)
Else
DateTime(Year(CurrentDate),Month(CurrentDate)+1,1,23,59,59)-1

// If Current Quarter
Else
If {?Predefined Date Range} = &quot;Current Quarter&quot; Then
If Month (CurrentDate) > 9 Then
DateTime(Year(CurrentDate),12,31,23,59,59)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)+2)/3)+1,1,23,59,59)-1

// If Current Year
Else
If {?Predefined Date Range} = &quot;Current Year&quot; Then
DateTime(Year(CurrentDate),12,31,23,59,59)

// If Today
Else
If {?Predefined Date Range} = &quot;Today&quot; Then
DateTime(Year(CurrentDate),Month(CurrentDate),Day(CurrentDate),23,59,59)

// If Custom Date Range
Else
If {?Predefined Date Range} = &quot;Custom Date Range&quot; Then
dateadd('s',-1,(Maximum({?Custom Date Range})+1))

// This code should never be invoked
Else
CurrentDate-1


// Record Selection Formula:
(
({SOL_VW_RPT_CLASS_SUM_DUR.LOCALTIME} >= {@Date Range Start})
and
({SOL_VW_RPT_CLASS_SUM_DUR.LOCALTIME} <= {@Date Range End})
)

and
...

I think that this version had the bugs ironed out.

-k

 

[Reebo99]

No, I don't think they did, however, you can find out if a record is in the lastfullquarter by using the following :

DateVar tempDate := {DateField};

(If DatePart(&quot;q&quot;,CurrentDate) = 1 then
(NumberVar tempYear := Year(CurrentDate)-1;
NumberVar tempQuarter := 4
else
(NumberVar tempYear := Year(CurrentDate);
NumberVar tempQuarter := DatePart(&quot;q&quot;,CurrentDate)-1);

If DatePart(&quot;q&quot;,tempDate) = tempQuarter and Year(tempDate) = tempYear then
&quot;Yes&quot; else
&quot;No&quot;

This will give you a Yes if a record is in the last quarter, a No if it isn't.....

Reebo
Scotland (Sunny with a Smile)

 

[Naith]

There is no Quarter functionality in v9, other than CalendarNthQtr.

Naith

 

[ChuckVanT]

Here is what I did. I put this in the Running Total Evaluate Formula:

Select Month(Today)
Case 1 to 3: Year({YOUR DATE FIELD}) = Year(Today)-1 AND Month({YOUR DATE FIELD}) in [10 to 12]
Case 4 to 6: Year({YOUR DATE FIELD}) = Year(Today) AND Month({YOUR DATE FIELD}) in [1 to 3]
Case 7 to 9: Year({YOUR DATE FIELD}) = Year(Today) AND Month({YOUR DATE FIELD}) in [4 to 6]
Case 10 to 12: Year({YOUR DATE FIELD}) = Year(Today) AND Month({YOUR DATE FIELD}) in [7 to 9]



Thanks for all the help!!!

Chuck

 

[Reebo99]

Naith,

Calendar1stQuarter will only report on items for the first quarter if the current year (I think?).

The problem is if currentdate = 1st Jan 2003 and you want to report on Calendar4thQuarter, it will try to get items from the 4th quarter 2003, not 4th quarter 2002.

However, you could use something like :

{DateField} in DateAdd(&quot;yyyy&quot;,-1,Minimum(calendar4thqtr)) to DateAdd(&quot;yyyy&quot;,-1,Maximum(calendar4thqtr))

to get the items from 4th quarter last year, but I don't think it would be the best solution.





Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

Brute force works...

I had thought you were trying to limit the rows in the report to the lastfullquarter, your posting was a bit vague, I should have just given the usual please post what you're really trying to do.

-k

 

[Naith]

Hey, I didn't say he should use the CalendarNthQtr functions, I just said those were the only quarter functions in v9. i.e. No LastFullQuarter function.

Naith

 

[Reebo99]

Sorry Naith, misunderstood your post.

My apologies mate.....

Reebo
Scotland (Sunny with a Smile)