Count by Custom Quarters

Using CR 2008

I am trying to develop a report that counts matters that were open at the end of each quarter.
This is a cross tab example as below.

          q1-1996       q2-1996
smith        10            15

The financial year runs from 1st May to 30th April of the next year

The quarters would be
Q1 – 1st May- 31st July
Q2 – 1st August – 31st October
Q3 - 1st November to 31st January
Q4 – 1st Feb to April 30th

There is data from the year 1996 onwards

At the moment the formula to get the column headings

if  opendate  < '1996-08-01 00:00:00.000' and closedate > '1996-08-01 00:00:00.000' then 'Q1 -1996' else
if  opendate  < '1996-11-01 00:00:00.000' and closedate > '1996-11-01 00:00:00.000' then 'Q2 -1996' else
etc. all the way through to the present quater

As you can imagine this is unwieldy and I have to keep adding new code when the next quarter comes along
Is there a better way of doing this?
Mark WilsonBI DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James0628Commented:
Based on your formula, opendate and closedate are string variables.  Do you have datetime fields with those values?  That would probably make things a bit simpler (as opposed to extracting pieces from the strings).

 James

 PS:
 Shouldn't the closedate test really be >=?  I don't suppose it matters unless the closedate could actually be at midnight, but if that did happen, the > tests would exclude it.

 PPS:
 There is also an apparent assumption here that something won't be open for more than 1 quarter.  For example, if something was opened in July and closed in November, it was open at the end of the first and second quarter, but you're only going to count it with the first quarter.
0
James0628Commented:
Assuming that you have datetime fields for the open and close date, and that nothing will be open for more than 1 quarter, I think a formula like this will work:

if CStr ({open field}, "MM-dd") < "08-01" and
 CStr ({close field}, "MM-dd") >= "08-01" then
  "Q1 - " + CStr ({open field}, "yyyy")
else
  if CStr ({open field}, "MM-dd") < "11-01" and
   CStr ({close field}, "MM-dd") >= "11-01" then
    "Q2 - " + CStr ({open field}, "yyyy")
  else
    if CStr ({open field}, "MM-dd") < "02-01" and
     CStr ({close field}, "MM-dd") >= "02-01" then
      "Q3 - " + CStr (DateAdd ("yyyy", -1, {close field}), "yyyy")
    else
      if CStr ({open field}, "MM-dd") < "05-01" and
       CStr ({close field}, "MM-dd") >= "05-01" then
        "Q4 - " + CStr (DateAdd ("yyyy", -1, {open field}), "yyyy")

Open in new window


 If things can be open for more than 1 quarter, the formula will need some tweaking.  For one thing, Q3 assumes that the close date will be in the next year, so it subtracts 1 from the close year to get the display year.  But if the close date could be 2 years later, that won't work.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
Try this formula

Local StringVar strQtr;
Local StringVar strFY;

If Month({Sheet1_.OpenDate}) >= 5 then
    strFY := CStr(Year({Sheet1_.OpenDate}),0,'')
ELSE 
    strFY := CStr(Year({Sheet1_.OpenDate}) - 1,0,'');

If Month({Sheet1_.OpenDate}) <= 1 then
    strQtr := '3'
Else if Month({Sheet1_.OpenDate}) <= 4 then
    strQtr := '4'
Else if Month({Sheet1_.OpenDate}) <= 7 then
    strQtr := '1'
Else if Month({Sheet1_.OpenDate}) <= 10 then
    strQtr := '2'
Else
        strQtr := '3';

strFY & '-Q' & strQtr

Open in new window


mlmcc
FY-Calc.rpt
fy-calc.xls
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

James0628Commented:
mlmcc,

 He's looking for the things that were open at the end of each quarter, so you need to check the close date too.  (I assume that something can be opened and closed in the same quarter)

 James
0
Mark WilsonBI DeveloperAuthor Commented:
Hi

Thanks for the answers

OpenDate and ClosedDate are datetime fields

A matter can be open for more than 1 quarter, some for instance are still open 5 years after open date
0
mlmccCommented:
Which field do you want to base the quarter on?

Do you need to count a work order in each quarter it is open?

mlmcc
0
Mark WilsonBI DeveloperAuthor Commented:
Thanks I used the formula and tweaked it for my needs
0
James0628Commented:
You're welcome.  Glad I could help.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.