Sum values approximately every 10mins 2

[Iliemoo]

Hi all, does anyone have some good idea to go about doing this? Approximately every 10mins, each part of a piece of hardware that has four parts in total will produce a value with a time stamp. The four parts are supposed to be synchronized but in reality one or two of them will differ about 1-2 minutes. So the data look something like this
Part 1 of4 Part 2 of4 Part 3 of4 Part 4 of4
2:01 2:02 2:01 2:01
2:11 2:12 2:12 2:11
2:22 2:23 2:22 2:22
...
with each time (actually datetime) stamp will have a number. I would very much like to sum the 4 values in my report. Notice the time stamp doesn't always hit the 10 minute mark. The 4 value sort of cluster at the not so clear 10min mark . I think this is a toughie, any input would be appreciated.

 

[lbass]

You could replace the time stamp value with a formula like:

round(hour(time({table.datetimestamp}))*60 + minute(time({table.datetimestamp})),-1)

This will round the field to the nearest 10 minutes. You could then group on this and insert a sum on the value field.

-LB

 

[Iliemoo]

That's a very good way if the time stamps are more or less near the 10 minute mark, however, it shifts over time and there could exist a time set such as 2:24 2:25 2:24 2:24 which will result in two groups.

 

[synapsevampire]

Please supply example data and expected output.

For instance you state that each will have a number that you'd like to sum.

Generally it's best to supply the table layout and data within, and then an example of what you'd like output.

-k

 

[kenhamady]

Are these 4 times and 4 numbers in seperate tables, separate records in one table, or all in the same record?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Iliemoo]

Thank you for your sugguestion, here's what my data looks like,
# of 4 DateTimeStamp Value
1 6/17/03 11:01 AM 2000
2 6/17/03 11:02 AM 4000
3 6/17/03 11:02 AM 5000
4 6/17/03 11:01 AM 5000
1 6/17/03 11:12 AM 5000
2 6/17/03 11:13 AM 4000
3 6/17/03 11:12 AM 2500
4 6/17/03 11:12 AM 5000
1 6/17/03 11:23 AM 5000
2 6/17/03 11:24 AM 4000
3 6/17/03 11:23 AM 5000
4 6/17/03 11:23 AM 5000
... ... ...
Output should be like,
TimeStamp Sum
6/17/03 11:01 AM 16000 (2000+4000+5000+5000)
6/17/03 11:12 AM 17500 (5000+4000+2500+5000)
6/17/03 11:23 AM 19000 (5000+4000+5000+5000)
... ...
The output time stamp could either be just any one of the four, or an average of the four, it just has to give an approximate idea where the sum occured. Notice how the time stamp shifts up and down now and then but they are relatively close within a group. Maybe I could have a formula that just sum four consecutive values, but then I have to make sure the sequance start on the correct record. Eventually I will have to find out when the maximum difference between the neighboring sum occurs, but that's another story. Thanks again!

 

[Ngolem]

hmmm...this is hard to group on the basis of time...

I wonder if CINT() is available in Crystal

My thinking is this...to group on the "rounded" value of time to the "10 minutes"

//@group1

datetimeVar test := {table.TimeStamp}
numbervar mins := round(minute(test)/10);
numbervar hours := hour(test)*60;
numberVar days := day(test);
numberVar temp;

temp := hours + mins;
if hours + mins = 1386 then // rounded mins at midnight
(
temp := 0;
days := days + 1;
);

totext(days,0) + " - " + totext(temp,0);

this formula works but perhaps should be more bullet proof to handle crossovers at midnight from one month to the next and one year to the next...this is easy enough to do if necessary

So the first group is on the basis of this formula...the next group is on the basis of "#of4" and the summing becomes trivial after that



Jim Broadbent

 

[Ngolem]

should be

datetimeVar test := {table.TimeStamp};

left off the semicolon


Jim Broadbent

 

[kenhamady]

This is tough because the data doesn't give you any reliable handle on the readings in one group.

Are the fired by the same time trigger or independently. I ask becuse they seem to wander together? That indicates a common trigger, which (in a perfect world) would provide the common ID to the group at the time of the trigger. If they are triggered independently, there isn't anything to prevent one from wandering ambiguously between groups.

Are there always exactly 4 in every set? If so, you could use variables to count to 4 and then reset.

Are they always after the 10 minute mark, or could one fire a few seconds before? IOW could you round all of them down to the 10.



Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Iliemoo]

Thank you Ngolem and Kenhamady. I did tried to round the time but then I realized there instances where there could a time set like 1:04 and 1:05 in the same group, but with rounding, they will be seperated . If only the data is better. Kenhamady you are right, they are triggered by the same timer I think, but the DBA during design phase did not include that infromation, and now I am left key-less. I think I will go wiht the 1 to 4 counter and reset idea. Thanks alot guys.

 

[Ngolem]

naw...this will fall apart for values of time straddling the "5 minute mark" ie: 1:14, 1:15, 1:14, 1:16

I see that now...the grouping would be off here for these types of situations.


How much data is there?? there is another way I can think of but its viability would depend on the amount of data.

Basically, would be to simply group by "#of4"

In the report header you would have an initialization formula for arrays

//@initialization (suppressed in Report header)

//assign 50% more than you need max 1000
whilePrintingrecords;
StringVar array Part1_value := ["","",.....,"",""];
datetimeVar array Part1_Time:= [datetime(0,0,0),datetime(0,0,0),.....,datetime(0,0,0),datetime(0,0,0)];
StringVar array Part2_value := ["","",....."",""];
datetimeVar array Part2_Time:= [datetime(0,0,0),datetime(0,0,0),.....,datetime(0,0,0),datetime(0,0,0)];
StringVar array Part3_value := ["","",....."",""];
datetimeVar array Part3_Time:= [datetime(0,0,0),datetime(0,0,0),.....,datetime(0,0,0),datetime(0,0,0)];
StringVar array Part4_value := ["","",....."",""];
datetimeVar array Part4_Time:= [datetime(0,0,0),datetime(0,0,0),.....,datetime(0,0,0),datetime(0,0,0)];
numberVar Pointer1 := 0;
numberVar Pointer2 := 0;
numberVar Pointer3 := 0;
numberVar Pointer4 := 0;

the report would be groupped on "#of4"
Now you place the following in the detail section

//@Collect data

whilePrintingrecords;
StringVar array Part1_value ;
datetimeVar array Part1_Time;
StringVar array Part2_value ;
datetimeVar array Part2_Time;
StringVar array Part3_value ;
datetimeVar array Part3_Time;
StringVar array Part4_value ;
datetimeVar array Part4_Time;
numberVar Pointer1 ;
numberVar Pointer2 ;
numberVar Pointer3 ;
numberVar Pointer4 ;

if {table.#of4} = 1 then
(
Pointer1 := Pointer1 + 1;
Part1_Time[Pointer1] := {table.timestamp};
Part1_Value[Pointer1] := totext({Table.value});
)
else if {table.#of4} = 2 then
(
Pointer2 := Pointer2 + 1;
Part1_Time[Pointer2] := {table.timestamp};
Part1_Value[Pointer2] := totext({Table.value});
)
else if {table.#of4} = 3 then
(
Pointer3 := Pointer3 + 1;
Part1_Time[Pointer3] := {table.timestamp};
Part1_Value[Pointer3] := totext({Table.value});
)
else if {table.#of4} = 4 then
(
Pointer4 := Pointer4 + 1;
Part1_Time[Pointer4] := {table.timestamp};
Part1_Value[Pointer4] := totext({Table.value});
);


now every section of the report is suppressed except for the Report Footer...in here you break up the section into subsections to display the results

at 20 chars/time stamp you will be able to print out 10 lines of data/formula

//@display Timestamp1

whilePrintingrecords;
datetimeVar array Part1_Time;
datetimeVar array Part2_Time;
datetimeVar array Part3_Time;
datetimeVar array Part4_Time;
StringVar array Part1_Value;
StringVar array Part2_Value;
StringVar array Part3_Value;
StringVar array Part4_Value;
numberVar Pointer ;
stringVar result ;
numberVar temp;
numberVar mins;
numberVar testtime;
StringVar final

for Pointer := 1 to 10 do
(
//only process a full set of numbers
if Part1_Value[Pointer] = "" or Part2_Value[Pointer] = "" or Part3_Value[Pointer] = "" or Part4_Value[Pointer] = "" then exit for;

result := "";
temp := 0;
//This handles the midnight situation
testtime := hour((Part1_Time[pointer])*60 + minute((Part1_Time[pointer])
if testtime < 5 then
temp := temp + 1439 + testtime
else
temp := temp + testtime;

testtime := hour((Part2_Time[pointer])*60 + minute((Part2_Time[pointer])
if testtime < 5 then
temp := temp + 1439 + testtime
else
temp := temp + testtime;
testtime := hour((Part3_Time[pointer])*60 + minute((Part3_Time[pointer])
if testtime < 5 then
temp := temp + 1439 + testtime
else
temp := temp + testtime;

testtime := hour((Part4_Time[pointer])*60 + minute((Part4_Time[pointer])
if testtime < 5 then
temp := temp + 1439 + testtime
else
temp := temp + testtime;

temp := temp/4

if temp > 1439 then
mins := temp - 1439
else
mins := temp;

result := totext(Part1_Time[pointer],&quot;dd/MM/yy&quot + &quot; &quot;
if temp > 1439 then
result : result + totext(maximum(hour(Part1_Time[pointer]),hour(Part2_Time[pointer]),hour(Part3_Time[pointer]),hour(Part4_Time[pointer])),0,&quot;&quot;,&quot;&quot
else
result : result + totext(minimum(hour(Part1_Time[pointer]),hour(Part2_Time[pointer]),hour(Part3_Time[pointer]),hour(Part4_Time[pointer])),0,&quot;&quot;,&quot;&quot

result := result + &quot;:&quot; + totext(mins,&quot;00&quot;
final := final + result + chr(13) + chr(10);
);

final;


now the last is easier

//@display Sum1

whilePrintingrecords;
StringVar array Part1_value ;
StringVar array Part2_value ;
StringVar array Part3_value ;
StringVar array Part4_value ;
numberVar Pointer ;
stringVar Finalresult := &quot;&quot;;

for Pointer := 1 to 10 do
(
Finalresult := Finalresult +
totext(tonumber(Part1_value[Pointer]) +
tonumber(Part2_value[Pointer]) +
tonumber(Part3_value[Pointer]) +
tonumber(Part4_value[Pointer]),0) +
chr(13) + chr(10);
);

FinalResult;

these 2 formulas are placed side-by-side and you create as many formula sets as you have initialized for the arrays/10

enable a suppress Blank section on each subsection and sections with no data will disappear.

I still didn't address midnight changes at monthly or yearly change but this should work














Jim Broadbent

 

[kenhamady]

Actually, I meant rounding DOWN.
So 104, 105 and 106 would both round to 100

But if you can get 159, 201 and 202, it wouldn't work.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com