finding current quarter

[mmwdmr]

Crystal 10 on Oracle

I am working on a report that uses a separate field for each month (1 - 12). If todays date is in one of the month buckets, I need to grab the value in that bucket and use it in a "quarter" formula. The value in that bucket is the percent complete on a construction job. For instance, if I look at the data today, there is a values of 40% in the month 3 bucket. I need to place this value in a group footer (by project). I know the formula to do this couldn't be that difficult but I just can't visualize the logic - and I'm sure it is because these are montly buckets, not quarterly buckets. In plain english, I want something like,

if todays date is in quarter 2 then grab the value in say, bucket 6 (June - current month). If it is month 6 there is a value in that bucket and the remaining 11 buckets are not populated. When it rolls into month/bucket 7, there is a value in month/bucket 7 and nothing in the remaining 11 buckets and then I need to be reporting on 3rd quarter.

Would it be better to have 4 formulas, one for each quarter or one formula that references all 12 buckets?

Thanks!

 

[satinsilhouette]

mmwdmr,

have you tried using the Calendar1stqtr (etc.) functions to do a case or if then else for your buckets? Possibly in conjunction with

if quarter(currentdate) = calendar1stqtr then.....

or

If {orders.ORDER DATE} in Calendar1stQtr Then

{orders.ORDER AMOUNT}

Else

0

??
Thanks!

 

[mmwdmr]

hey - how did you know that it was me!?

There are 12 buckets, one representing each month. The buckets themselves do not contain a date. They contain a percentage complete on the project. So, how would the calendar quarter function work when the value in these buckets is not a date?

Maybe your case statement suggestion is the resolution but I'm not sure how to write it...

The data looks something like this:

Bucket1 (Janaury) = 0.00
Bucket2 (February) = 0.00
Bucket3 (March) = 40.00
Bucket4 = 0.00
Bucket5 = 0.00
Bucket6 = 0.00
etc...

So, as of March 31 the project is 40% completed. First I have to snag the fiscal year of 06, then look at these monthly buckets. Then based on the current date, determine what month (which I need to represent quarterly) I need to grab.

Make any sense?

 

[satinsilhouette]

doesn't matter if the buckets are the % complete - they stay their buckets but you create a quarterly formula based off the start and end or just start or just end dates of the projects and their buckets:

something like

If startdate and enddate in calendar1stqtr then (bucket1 + bucket2 + bucket3) else
if startdate and enddate in calendar2ndqtr then (bucket4 + bucket5 + bucket6) else if
startdate and enddaate in calendar3rdqtr then (bucket7 + bucket8 + bucket9) else

(bucket10 + bucket11 + bucket12)

 

[satinsilhouette]

Oh and they had to get those percentages stuck in those buckets somehow by a date, so look for the date field they were using in their code to make the buckets up.

 

[mmwdmr]

Still didn't tell me how you knew it was me!

Anyway, I understand your logic now but I was playing around in the meantime and I started experimenting with another way. I will post the alternate solution if it works.

Later!

 

[satinsilhouette]

Okay, I just knew by the example you posted it was you.

 

[mmwdmr]

My alternative solution is a long explanation but...

The date the percentage was entered is "service date" in the system. Since I also had to test for fiscal year, what I did was checked to see if the year of the service date was in the prior fiscal year or the current fiscal year. If there were amounts in the prior fiscal year buckets, I summed them and carried them forward. Then I added the buckets for the current fiscal year (which will have a value in a bucket no greater than month 6) to the prior to get the total estimated % complete for year end. I basically did the same for the quarter end by not adding in the prior year and just looking at the buckets for fiscal year 6. There will never be a value in a monthly bucket past the current month for the current fiscal year so it worked out!

 

[satinsilhouette]

That would work as well. Especially since they will probably want to know previous plus current amount of work done.

Good thinking out of the box!