I'm working in crystal 8.5. My question is how do i find the last day of the previous quarter using the last day of my "base" quarter? For example, if the last day of the base quarter is 6/30/2009, what is the appropriate formula to use in order to find the last day of previous quarter, 3/31/2009.

 

 

Or, is there a way to find the last date in a pool of records. I cannot use the Maximum function as i have running totals in the report.

thanks,

cecil

thanks for your reply.

 

Iam building a dynamic column report with 12 columns and 30 rows of data. The columns represent end of quarters, i.e. 3/31/2009, 12/31/2009, 9/30/2009, etc. I would like the last column or column 12 to be the most recent quarter. Column 11 the previous quarter before that and so on for columns 10 to 1. From here i will calculate row totals based on the column headings. I hope this makes sense. when subtracting quarters using the  dateadd fuction it returns the correct month but not correct last day of quarter. For example if last 6/30/2009 the previous quarter displays as 3/30/2009 instead of 3/31/2009. The dates are no important as i can use somthing like Q1 2009 or Q2 2009 to display the column headings. if i do this, how do i find the last date in the the data set without usining an array function. Or is this even possible. If this makes no sense i'll try to explain this way.....my report looks like this

 

 

                     Q1 Y1    Q2 Yr1   Q3 Yr1   Q4 Yr1    Q1 Yr2    Q2 Yr3    Etc

 

Variable1

Variable2

Variable 3

 

 

I am trying to total each variable for each quarter and year

 

Cecil

 

 

			Productivity
	Cost Center/		Index
	Pay Period End Date		w/Orien
Group 1	Director ABC
Group 3	20100206	PP Total	3,554.29%
	20100220	PP Total	3,218.98%
	20100306	PP Total	3,211.06%
Group 2	77610	Department X	3,320.31%
	20100206	PP Total	108.37%
	20100220	PP Total	103.71%
	20100306	PP Total	104.06%
	77620	Department Y	105.33%
	20100206	PP Total	139.51%
	20100220	PP Total	208.92%
	20100306	PP Total	104.61%
	83110	Department Z	139.48%
	Director ABC		172.50%

 

 

When we re-write the report to specify the payperiod end dates we are only getting the last pay periods data...the prior two pay periods do not populate.  Any suggestions on how to make this work?

 

 

 

 

 

			02/06/10	02/20/10	03/06/10
			Index	Index	Index
Cost Center/Department			w/Orien	w/Orien	w/Orien
Director ABC
77610	Department X		0.00%	0.00%	3,320.31%
77620	Department Y		0.00%	0.00%	105.33%
83110	Department Z		0.00%	0.00%	139.48%
Director ABC

 

 

 

Week 1

IF {DS_PMR_DOLLARS_AND_HOURS.TIME_FRAME_END_DATE} = 20100206 THEN
Sum ( mailto:%7b@TARGET - {@TARGET WORK HOURS}, {DS_COST_CATEGORY.DEPARTMENT})%
Sum ( mailto:%7b@ActWrkFTE - {@ActWrkFTE }, {DS_COST_CATEGORY.DEPARTMENT})

 

Week 2

F {DS_PMR_DOLLARS_AND_HOURS.TIME_FRAME_END_DATE} = 20100220 THEN
Sum ( mailto:%7b@TARGET - {@TARGET WORK HOURS}, {DS_COST_CATEGORY.DEPARTMENT})%
Sum ( mailto:%7b@ActWrkFTE - {@ActWrkFTE }, {DS_COST_CATEGORY.DEPARTMENT})

 

Week 3

IF {DS_PMR_DOLLARS_AND_HOURS.TIME_FRAME_END_DATE} = 20100306 then
Sum ( mailto:%7b@TARGET - {@TARGET WORK HOURS}, {DS_COST_CATEGORY.DEPARTMENT})%
Sum ( mailto:%7b@ActWrkFTE - {@ActWrkFTE }, {DS_COST_CATEGORY.DEPARTMENT})

 

phisel and I work together and appears you may be working on the same issue for both of us.  feel free to respond to either one of us.  Thank you for your prompt responses!

I just responded to your other post so this is making more sense between the two of them.

If you want to go down this path you will need to use Running Totals (RTs) or variable formulas to get your numbers.

I use RTs...

YoOu have agroup on director then a group on department.

try making 2 RTs for each value you want

Name=TargetWeek1

Field to summarize= mailto:summarize=%7b@TARGET - {@TARGET WORK HOURS}

Type of SUmmary=SUM

evaluate=use a formula

{DS_PMR_DOLLARS_AND_HOURS.TIME_FRAME_END_DATE} = 20100206

Reset=on group1 (assuming I had your group set up correct)

 

Now for week one create your formula as

{#TargetWeek1} % {#ActWrkFTEWeek1}

Place on Group footer1 to see the % for week one.

 

 

Repeat process for week 2 but change your evaluate formula in those RTs to use

{DS_PMR_DOLLARS_AND_HOURS.TIME_FRAME_END_DATE} = 20100220

 

repeat per weeks you want.