Calculate Headway Deviation 1

[capronton]

I have the data set below and I would like to calculate headway deviation (actual headway - schedule headway)

tDate [/indent]incTime schTime bus HeadwayDeviation
3/23/16 6:39:30 6:37 5015 ?
3/23/16 6:47:30 6:49 5012 ?
3/23/16 7:02:30 7:01 5007 ?
3/23/16 7:34:30 7:26 5022 ?
3/23/16 7:38:30 7:38 5006
3/23/16 7:55:31 7:50 5017
3/23/16 8:10:32 8:05 5003
3/23/16 8:28:31 8:17 5013
3/23/16 9:27:32 9:22 5010
3/23/16 9:37:32 9:35 5019
3/23/16 9:43:32 8:30 5009

Formula for ActualHeadway
previous(incTime) - incTime

Formula for ScheduleHeadway
previous(schTime) - schTime

Formula for HeadwayDeviation
ActualHeadway - ScheduleHeadway

Can someone assist me in creating the 3 formulas above based on data set?

Thanks in advance for assistance.

 

[LaurieHamlin]

What version are you using? If you need simple subtraction as your post suggests, just copy exactly what you have there.

 

[capronton]

Thanks Laurie. I'm using Crystal Reports X1.

Can you explain why the formula below gets a missing parentheses error?
I would like the difference in seconds of the two dates below.

DateDiff (s,{incident_log.sched_time},previous({incident_log.sched_time}))

 

[capronton]

I figured it out. I forgot my double quotes around the "interval" value. See below.
Thanks.

DateDiff ("s",{incident_log.sched_time},previous({incident_log.sched_time}))

 

[capronton]

I have another problem, can someone advise?

I get the error "field cannot be summarized" with the formula below. Can someone explain why?
How can I get a count of @HeadwayStatus? Also, none of these formulas are available in the RunningTotal.
Thanks in advance.

count({@HeadwayStatus})

Formula for @HeadwayStatus is below:
If {@hdwyDeviation} <= -5 then "Early" else
If {@hdwyDeviation} >= 5 then "Late" else "OnTime"

Formuala for @hdwyDeviation is below:
{@actualHeadway} - {@schedHeadway}

Formual for @actualHeadway is below:
DateDiff ("n", previous({incident_log.incident_date_time}),{incident_log.incident_date_time})

 

[CrystalizeCanada]

The reason you cannot use either the built in Summary or the built in Running Total field stems from your original formula.

You are using the function Previous in the formula @actualHeadway. That function, which is a very useful function, is from the Print State category of functions and any formula that uses any functions in this category calculate WhilePrintingRecords. (The IsNull function is an exception and doesn't really belong in this category).

Formulas calculate at one of three different times:
BeforeReadingRecords

e.g. currentdate - 1​

These formulas calculate once per report​

WhileReadingRecords

e.g. {Table.Field} * {Table.Field2}​

These formulas calculate once per record​

WhilePrintingRecords

e.g. Previous({Table.Feild})​

These formulas calculate based on which section you place them in​

Only formulas that calculate WhileReadingRecords can be summarized using the built in Summary or can have a Running Total used on them.
And any formula that calculate WhilePrintingRecords cannot be summarized since Crystal executes the built in Summary and Running Total before it processes the WhilePrintingRecords formulas.

So if you have a WhilePrintingRecords formula and you want to summarize it you have to do the sum yourself using variables.
A set of three formulas is normally used. One to accumulate the value (placed in the detail or group). One to Reset (Placed often in the Group Header) and one to Display. The Reset formula can be skipped if you are not resetting the variable when the group changes.
Below are an example of the formulas that might give you an idea on how to do your own total.

//Formula 1 - Accumulate Formula - placed in the detail section (or sometimes in the lower level group)
WhilePrintingRecords;
numberVar EarlyCount;
numberVar LateCount;
numberVAr OnTimeCount;
EarlyCount := EarlyCount + if {@HeadwayStatus} = "Early" then 1 else 0;
LateCount := LateCount + if {@HeadwayStatus} = "Late" then 1 else 0;
OnTimeCount := OnTimeCount + if {@HeadwayStatus} = "OnTime" then 1 else 0;

//Formula 2 - Reset Formula - this is normally placed in the Group Header but can be skipped if the variables do not need to be reset
WhilePrintingRecords;
numberVar EarlyCount := 0;
numberVar LateCount := 0;
numberVar OnTimeCount := 0;

//Formula 3 - Display Formula - This is placed normally in the Group footer or Report Footer
WhilePrintingRecords;
numberVar EarlyCount; //Comment out the line you don't want
numberVar LateCount; //Comment out the line you don't want
numberVar OnTimeCount; //Comment out the line you don't want

Another alternative to this set of three print time formulas is to redo your original formula without using the Previous function so it calculates WhileReadingRecords. That can sometimes be a better solution.

Gordon BOCP
Crystalize

 

[capronton]

Excellent explanation! This was very helpful! Thanks.

 

[capronton]

For some reason my @OnTimeCount and my @TotalCount (detail number) is one less than my @OnTimeCount and @TotalCount (summary number).

Can someone explain what may be going on?

 

[CrystalizeCanada]

If you have an @OnTimeCount formula in the detail section and also place the same formula in the summary section you will get another 1 added to the variable (assuming the last record was an 'OnTime' record).

The reason is this formula is a Print-Time formula and Print-Time formulas run based on placement.
@OnTimeCount will run for each record (since it is in the details) and run one more time in the summary section (like a report or group footer).
It will therefore increment the variable by 1 if placed in the summary section.
Consequently the formulas should not be placed again in the summary section.
You need a separate display formula that just displays the variable value (and doesn't not increment it by 1).

Its only appears to be a problem with the @OnTimeCount and not the others formulas because the last record happens to be an 'OnTime' record.
If you have done the same with the other formulas @EarlyCount and @LateCOunt and also placed them in the summary section (as well as details) it may seem to work however that is just lucky - if the last record happened to be 'Early' or 'Late' they would also increment.
You should not place those in the summary sections either.

You will need three separate Display formulas to be placed in the summary section.
That is the Formula 3 in the previous posting (and below).

//Formula 3 - Display Formula - This is placed normally in the Group footer or Report Footer
WhilePrintingRecords;
numberVar EarlyCount; //Comment out the line you don't want
numberVar LateCount; //Comment out the line you don't want
numberVar OnTimeCount; //Comment out the line you don't want


Gordon BOCP
Crystalize

 

[capronton]

Below is my Display formula, however I can only display the "TotalCount" variable.
How can I display the other variables?

WhilePrintingRecords;
numberVar EarlyCount;
numberVar LateCount;
numberVar OnTimeCount;
numberVar OutOfOrder;
numberVar TotalCount;

 

[CrystalizeCanada]

create 5 separate formulas:

WhilePrintingRecords;
numberVar EarlyCount;

WhilePrintingRecords;
numberVar LateCount;

WhilePrintingRecords;
numberVar OnTimeCount;

WhilePrintingRecords;
numberVar OutOfOrder;

WhilePrintingRecords;
numberVar TotalCount;

Gordon BOCP
Crystalize

 

[capronton]

Great Thanks!!!

 

[capronton]

One more thing. The formula below is taking the difference between "suppressed" records. How can I get the difference of only records not suppressed?

Formula for "ActualHeadway"
DateDiff ("n", previous({incident_log.incident_date_time}),{incident_log.incident_date_time})

 

[CrystalizeCanada]

You can try incorporating the condition for suppression into your ActualHeadway Formula:

if PutYourConditionForSuppressionHere then 0 else DateDiff ("n", previous({incident_log.incident_date_time}),{incident_log.incident_date_time})

Gordon BOCP
Crystalize

 

[capronton]

Thanks. Adding my "SuppressionCondition" appeared to have no affect. Basically I want to subtract the minimum "incTime" from the data set below. Can i use the "minimum" function in the statement below to subtract the minimum "incTime" from duplicate "schTime" records? Formula similar to the statement below. Data Set is also below.

If schTime = previous(schTime) then
subtract minimum((previous(incTime) - incTime))

tDate incTime schTime startTrip endTrip bus
4/5/16 5:00:17 4:58 4:58 AM 6:22 AM 5013
4/5/16 5:14:18 5:13 5:13 AM 6:37 AM 5015
4/5/16 5:29:18 5:28 5:28 AM 6:52 AM 5020
4/5/16 5:44:18 5:43 5:43 AM 7:07 AM 5009
4/5/16 6:01:18 5:58 5:58 AM 7:22 AM 5017
4/5/16 6:14:18 6:13 6:13 AM 7:37 AM 5011
4/5/16 6:22:19 6:21 6:21 AM 7:59 AM 5022
4/5/16 6:15:18 6:36 6:36 AM 8:14 AM 5001
4/5/16 6:16:18 6:36 6:36 AM 8:14 AM 5001
4/5/16 6:34:19 6:36 6:36 AM 8:14 AM 5001
4/5/16 6:52:19 6:51 6:51 AM 8:29 AM 5003
4/5/16 7:04:19 7:03 7:03 AM 8:41 AM 5019
4/5/16 7:06:19 7:03 7:03 AM 8:41 AM 5019
4/5/16 7:10:19 7:03 7:03 AM 8:41 AM 5019
4/5/16 7:12:19 7:03 7:03 AM 8:41 AM 5019
4/5/16 7:17:19 7:15 7:15 AM 8:53 AM 5014
4/5/16 7:18:19 7:15 7:15 AM 8:53 AM 5014
4/5/16 7:19:19 7:15 7:15 AM 8:53 AM 5014
4/5/16 7:28:19 7:27 7:27 AM 9:05 AM 5002
4/5/16 7:33:19 7:27 7:27 AM 9:05 AM 5002
4/5/16 7:42:20 7:27 7:27 AM 9:05 AM 5002
4/5/16 7:43:20 7:27 7:27 AM 9:05 AM 5002
4/5/16 7:40:20 7:39 7:39 AM 9:17 AM 5005
4/5/16 7:53:20 7:51 7:51 AM 9:29 AM 5021
4/5/16 8:04:20 8:03 8:03 AM 9:41 AM 5018
4/5/16 8:16:20 8:15 8:15 AM 9:53 AM 5010
4/5/16 8:35:20 8:32 8:32 AM 10:02 AM 5013
4/5/16 8:49:20 8:45 8:45 AM 10:15 AM 5015
4/5/16 8:50:20 8:45 8:45 AM 10:15 AM 5015
4/5/16 8:59:21 8:58 8:58 AM 10:30 AM 5020
4/5/16 9:01:21 8:58 8:58 AM 10:30 AM 5020
4/5/16 8:47:20 9:11 9:11 AM 10:43 AM 5009
4/5/16 8:48:20 9:11 9:11 AM 10:43 AM 5009
4/5/16 8:49:20 9:11 9:11 AM 10:43 AM 5009
4/5/16 8:50:20 9:11 9:11 AM 10:43 AM 5009
4/5/16 9:15:21 9:11 9:11 AM 10:43 AM 5009

 

[capronton]

Is it possible to write a statement in the Selection criteria that only pulls in minimum "incTime" records if I have duplicate "schTime" records?

 

[CrystalizeCanada]

The minimum function used with an array will pick out the lowest of a list of values.

So minimum( [ 88, 99, 22, 77 ] ) gives us 22
minimum( [ field, previous(field) ] ) will give you the lowest value between the field and the previous(field)

If schTime = previous(schTime) then minimum([(previous(incTime), incTime]) may be what you are trying to accomplish

Gordon BOCP
Crystalize

 

[capronton]

Great! Once I get the minimum value then I want the difference between the two minimum values but cannot get the formula below to work. How can I get the difference between two minimum values?

I get the "not enough arguments to this function has been given" error

DateDiff ("n", minimum( [ {incident_log.incident_date_time}, previous({incident_log.incident_date_time}) ] ))

 

[CrystalizeCanada]

The DateDiff function takes 3 arguments.

You are providing only two in your example hence the error.

DateDiff ("n", [highlight #FCE94F] minimum( [ {incident_log.incident_date_time}, previous({incident_log.incident_date_time}) ] )[/highlight],{incident_log.incident_date_time})

This maybe what you are intending.

Gordon BOCP
Crystalize

 

[capronton]

The program is working great now. Thanks for all your help with this!!!