Summing a 'n' datediff > a specific amount of minutes

[careymw]

Crystal 2008

I am trying to build a report which groups emails resolved
in <=8hr, <= 12hr and <=24hr.

I had this working until with this fromula: Datediff
datediff('n', ({KC_MESSAGEHISTORY.ACTIONDATE}), {KC_MESSAGE.COMPLETEDATE})

Then I created 3 formulas
Resp<=8 : If {@DateDiff} <= 480 then 1 else 0
Resp<=12 : If {@datediff} <= 720 then 1 else 0
Resp<=24 : If {@DateDiff} <= 1440 then 1 else 0

I summed these up using a RT and things worked fine.

I then discovered that email may have more then one action_date.
So now I need to take the MINIMUM(action_date)
Now I get an error that says a RT can't refer to a print time formula. Any suggestions on how I can get around this?

 

[IanWaterman]

YOu can replace your RTs with variables.

Change your datediff formula to use minium function

@reset

Whileprintingrecords;
global numbervar resp1:=0;
global numbervar resp2:=0;
global numbervar resp3:=0;

Place this where in header at level your RT was resetting.

@eval

Whileprintingrecords;
global numbervar resp1:=0;
global numbervar resp2:=0;
global numbervar resp3:=0;

If {@DateDiff} <= 480 then resp1:= resp1+1;
If {@datediff} <= 720 then resp2:= resp2+1;
If {@DateDiff} <= 1440 then resp3:= resp3+1;
Place this a point where data is evaluated

You will then need to 3 display formula, placed in Footer where RT was.

@resp1

Whileprintingrecords;
global numbervar resp1;

Ian

 

[careymw]

Thank you for the suggestion.
I set up with the details above. Its not giving me results I expect.

I have the following groups
group 1 by product
group 2 by Messageid

My RT was set up to evaluate on change of group messageid and then Reset on change of group product.

I placed the eval formula in the GH2
I placed the reset formula in GH1
I placed the 3 display formula in GF1

I see the eval fomula displaying 0 or 1 but the GF1 totals aren't calculating.

 

[lbass]

Ian forgot to remove the variable assignments (0's):

//@eval:
Whileprintingrecords;
global numbervar resp1;
global numbervar resp2;
global numbervar resp3;
If {@DateDiff} <= 480 then resp1:= resp1+1;
If {@datediff} <= 720 then resp2:= resp2+1;
If {@DateDiff} <= 1440 then resp3:= resp3+1;

-LB

 

[IanWaterman]

LB

Well spotted, that's the trouble with using copy and paste indescriminately ;-)

Ian

 

[careymw]

Thank you both. I haven't worked much with variables. I dug out my book and looked at that formula several times. I changed it different ways but didn't pick up on that.

I appreciate your assistance. This worked great.

 

[careymw]

In addition to these <8hr, <12hr, <24hr counts of @datediff I also need a total of the @datediff so that I can get an average response time.

I tried to include a global variable for this but it isn't summing the amounts. When I display the formula in my footer it shows the datediff for only my last record.

 

[IanWaterman]

You could also just add the 3 vars togther in a 4th formula

@total
Whileprintingrecords;
global numbervar resp1 + global numbervar resp2 + global numbervar resp3;

Ian

 

[careymw]

Yes I thought that as well but there are some emails that weren't responded too with in the 8, 12, or 24 hour groups. So I was missing some time.

I discovered that I could set it up like this:

whileprintingrecords;
global numbervar totaldatediff;

totaldatediff := totaldatediff + {@datediff};