complicated way of dividing data

[bertusavius]

I have a table with a list of events that have ID, two time-values and two 'trip values'. ID Timestart Timestop tripstart tripstop 25 6:55 7:55 259010 259030 25 7:06 7:55 259011 259030 25 7:11 7:57 259013 259030 25 7:17 7:57 259014 259030 25 7:23 7:57 259015 259030 25 7:27 7:59 259017 259031 25 7:29 7:58 259017 259031 25 7:40 7:59 259025 259031 25 8:11 8:21 259038 259043 25 8:41 9:07 259049 259063 25 8:48 9:16 259054 259064 78 13:24 13:34 259116 259122 78 13:40 14:14 259122 259134 78 13:45 14:22 259124 259136 78 13:54 14:22 259127 259136 78 14:03 14:23 259130 259136 78 14:35 14:58 259144 259148 78 14:36 14:58 259144 259148 78 14:43 15:06 259145 259150 78 14:52 15:38 259146 259162 78 15:19 15:42 259155 259163 78 15:27 15:47 259157 259164 78 15:54 16:02 259165 259167 <TBODY> </TBODY>

<TBODY>
</TBODY>

To make things less abstract:

The trip-value is the value of a trip meter in a car in kilometers.

So the top record actualy says:

car nr 25 had an event that started at 06:55 at trip value 259010 and this event stopped at 07:55 coinciding with trip value 259030 You could say this is a list of events with corresponding accumulating properties, so wether the timeID starts or stops doesnt really matter. All the matters is that a certain time corresponds with a certain trip value. Now wat I'd like is to create a measure that transforms and divides this information like this: ID binID distance travelled 25 0700-0800 (value in kms) 25 0800-0900 (value in kms) 25 0900-1000 (value in kms) 78 0700-0800 (value in kms) 78 0800-0900 (value in kms) 78 0900-1000 (value in kms) <TBODY> </TBODY> <TBODY> </TBODY>

<TBODY>
</TBODY>

I have a table to relate to which contains binID, binstart and binstop

I also have a table for dates

Is it even worth contemplating to solve this in Powerpivot, because it seems quite daunting to me atm.

<TBODY>
</TBODY>

 

[b.downey]

Are the "BinIDs" a reference to the "TimeStart"? or something else?

 

[bertusavius]

The bin-ID's represent a time window.
The refer to the timestart and timestop columns, yes.

I found a pretty similar problem on the MS SQL forum:
T-SQL 2005 to get 15 minutes SalesAmout average?

I gues the topic title should include the word 'quantizing data' or 'rounding data'.


The problem would be easier to imagine if the time values were rounded to whole hours. It would then be easier to calculate the trip difference between to time values.
The tricky part is to extrapolate the right trip data that would correspond to a round time value.

 

[bertusavius]

The bin-ID's represent a time window.
The refer to the timestart and timestop columns, yes.

I found a pretty similar problem on the MS SQL forum:
T-SQL 2005 to get 15 minutes SalesAmout average?

I gues the topic title should include the word 'quantizing data' or 'rounding data'.


The problem would be easier to imagine if the time values were rounded to whole hours. It would then be easier to calculate the trip difference between to time values.
The tricky part is to extrapolate the right trip data that would correspond to a round time value.

 

[bertusavius]

A bit more clarification(hopefully):

1) (raw data)
unrounded time A	trip value A
unrounded time B	trip value B
2) (step 1 of transformation)
rounded time A	corrected trip value A
rounded time B	corrected trip value B

<COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 6034" width=165><TBODY>
</TBODY>

3) (step 2 of transformation)
from rounded time A to rounded time B	corrected trip value B minus corrected trip value A
equals:
distance travelled in time bin of rounded time (ie one hour)

<COLGROUP><COL style="WIDTH: 220pt; mso-width-source: userset; mso-width-alt: 10715" width=293><COL style="WIDTH: 268pt; mso-width-source: userset; mso-width-alt: 13056" width=357><TBODY>
</TBODY>

 

[Laurent C]

Hi, you might want to start with a calculated column:
[estimated speed] = ([tripstop] -[tripstart]) / ([timestop] - [timestart])

At that point, this will turn to be a bucket problem, like the one you had before, except this time, you will have to multiply the calculated "time in bucket" by [estimated speed].

 

[b.downey]

If I look at the first entry

ID	Timestart	Timestop	tripstart	tripstop
25	6:55	7:55	259010	259030

<TBODY>
</TBODY>

The difference between the Trip Start and Trip Stop is 20

Based on the Bins, 5 Mins would be in the 6:00 hour and 55 mins in the 7:00 hour. Should we be taling 5/60 percentage of the 20K and assign it to the 6:00 Bin and 55/60 percentage of the 20K and assign it to the 7:00 bin?

 

[bertusavius]

@mr C:
interesting approach. As long as the intervals are small and there are many events per hour, the averages work quite well.
But I'm affraid they're not accurate enough.

I'd like the cumulative values of the buckets to be as close as or equal to the real trip data.




@b.downey
That is absolutely correct


I wonder if this isn't some kind of standard mathematic problem.
Quantizing a set of data in predifined bits.

Ecspecially when you imagine the start and stop data to be interchangable:

ID - TimeValue - Tripvalue

 

[b.downey]

Here is some VBA code that will produce the results requested

It expects the data to be in a Sheet Called "Sheet1" (no spaces) and the results (matrix) will be placed in "Sheet2". The code escentially prorates the miles accross the Bins based on the minutes in each BIN

Option Explicit
Type typRec
    ID As Integer
    BinArray(24) As Double
End Type
Sub Calc()
    Dim Rec() As typRec
    ReDim Rec(0)
    Dim ws As Worksheet
    
    Dim RowNo As Long
    
    Dim StartTime As Date
    Dim EndTime As Date
    
    Dim IdIdx As Integer
    Dim BinIdx As Integer
    Dim HourCnt As Integer
    Dim Miles As Long
    
    Dim TotalMin As Long
    Dim Min As Integer
    
    Dim Perc As Single
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    RowNo = 2
    For RowNo = 2 To 24
        IdIdx = FindIdx(ws.Cells(RowNo, 1), Rec)
        StartTime = ws.Cells(RowNo, 2)
        EndTime = ws.Cells(RowNo, 3)
        Miles = ws.Cells(RowNo, 5) - ws.Cells(RowNo, 4)
        HourCnt = Hour(EndTime) - Hour(StartTime)
        
        Select Case HourCnt
            Case 0
                BinIdx = Hour(StartTime)
                Rec(IdIdx).BinArray(BinIdx) = Rec(IdIdx).BinArray(BinIdx) + Miles
            Case Is > 0
                TotalMin = DateDiff("n", StartTime, EndTime)
                '***** Determine fractional time for 1st hour
                BinIdx = Hour(StartTime)
                Min = DateDiff("n", StartTime, CDate(BinIdx + 1 & ":00:00"))
                Perc = Min / TotalMin
                Rec(IdIdx).BinArray(BinIdx) = Rec(IdIdx).BinArray(BinIdx) + (Miles * Perc)
            
                Perc = 60 / TotalMin
                For BinIdx = Hour(StartTime) + 1 To Hour(EndTime) - 1
                    Rec(IdIdx).BinArray(BinIdx) = Rec(IdIdx).BinArray(BinIdx) + (Miles * Perc)
                Next BinIdx
                
                '***** Determine fractional time for Last hour
                BinIdx = Hour(EndTime)
                Min = DateDiff("n", CDate(BinIdx & ":00:00"), EndTime)
                Perc = Min / TotalMin
                Rec(IdIdx).BinArray(BinIdx) = Rec(IdIdx).BinArray(BinIdx) + (Miles * Perc)
            Case Else
    
        End Select
    Next RowNo
    
Call OutputResults(Rec)
End Sub
Function OutputResults(Rec() As typRec)
    Dim I As Integer
    Dim BinIdx As Integer
    Dim ws As Worksheet
    Dim RowNo As Long
    
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    ws.Cells.ClearContents
    RowNo = 2
    For I = 1 To UBound(Rec)
        For BinIdx = 1 To 24
            If Rec(I).BinArray(BinIdx) > 0 Then
                ws.Cells(RowNo, 1) = Rec(I).ID
                ws.Cells(RowNo, 2) = BinIdx
                ws.Cells(RowNo, 3) = Rec(I).BinArray(BinIdx)
                RowNo = RowNo + 1
            End If
        Next BinIdx
    Next I
End Function
Function FindIdx(ID As Integer, Rec() As typRec) As Integer
    Dim I As Integer
    
    For I = 1 To UBound(Rec)
        If Rec(I).ID = ID Then
            FindIdx = I
            Exit Function
        End If
    Next I
    
    ReDim Preserve Rec(I)
    Rec(I).ID = ID
    FindIdx = I
    
End Function

 

[bertusavius]

I really appreciate your help here.
Your solution works very well asigning the proper trip data to the right bins.
The problem is that it accumulates to total trip data.

Attached a schematic representation of the problem.
I hope it clarifies things.
I'm affraid it's in Dutch, but I think it speaks for itself.