complicated way of dividing data

[bertusavius]

[TABLE="width: 635"]
<TBODY>[TR]
[TD="width: 847, bgcolor: transparent"]I have a table with a list of events that have ID, two time-values and two 'trip values'.



[TABLE="class: grid, width: 200"]
<TBODY>[TR]
[TD="width: 21, bgcolor: #4f81bd"]ID
[/TD]
[TD="width: 66, bgcolor: #4f81bd"]Timestart
[/TD]
[TD="width: 65, bgcolor: #4f81bd"]Timestop
[/TD]
[TD="width: 57, bgcolor: #4f81bd"]tripstart
[/TD]
[TD="width: 56, bgcolor: #4f81bd"]tripstop
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]6:55
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:55
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259010
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259030
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]7:06
[/TD]
[TD="bgcolor: transparent, align: right"]7:55
[/TD]
[TD="bgcolor: transparent, align: right"]259011
[/TD]
[TD="bgcolor: transparent, align: right"]259030
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:11
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:57
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259013
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259030
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]7:17
[/TD]
[TD="bgcolor: transparent, align: right"]7:57
[/TD]
[TD="bgcolor: transparent, align: right"]259014
[/TD]
[TD="bgcolor: transparent, align: right"]259030
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:23
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:57
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259015
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259030
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]7:27
[/TD]
[TD="bgcolor: transparent, align: right"]7:59
[/TD]
[TD="bgcolor: transparent, align: right"]259017
[/TD]
[TD="bgcolor: transparent, align: right"]259031
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:29
[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:58
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259017
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259031
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]7:40
[/TD]
[TD="bgcolor: transparent, align: right"]7:59
[/TD]
[TD="bgcolor: transparent, align: right"]259025
[/TD]
[TD="bgcolor: transparent, align: right"]259031
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]8:11
[/TD]
[TD="bgcolor: #dce6f1, align: right"]8:21
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259038
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259043
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent, align: right"]8:41
[/TD]
[TD="bgcolor: transparent, align: right"]9:07
[/TD]
[TD="bgcolor: transparent, align: right"]259049
[/TD]
[TD="bgcolor: transparent, align: right"]259063
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1, align: right"]8:48
[/TD]
[TD="bgcolor: #dce6f1, align: right"]9:16
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259054
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259064
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]13:24
[/TD]
[TD="bgcolor: #dce6f1, align: right"]13:34
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259116
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259122
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]13:40
[/TD]
[TD="bgcolor: transparent, align: right"]14:14
[/TD]
[TD="bgcolor: transparent, align: right"]259122
[/TD]
[TD="bgcolor: transparent, align: right"]259134
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]13:45
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:22
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259124
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259136
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]13:54
[/TD]
[TD="bgcolor: transparent, align: right"]14:22
[/TD]
[TD="bgcolor: transparent, align: right"]259127
[/TD]
[TD="bgcolor: transparent, align: right"]259136
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:03
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:23
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259130
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259136
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]14:35
[/TD]
[TD="bgcolor: transparent, align: right"]14:58
[/TD]
[TD="bgcolor: transparent, align: right"]259144
[/TD]
[TD="bgcolor: transparent, align: right"]259148
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:36
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:58
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259144
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259148
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]14:43
[/TD]
[TD="bgcolor: transparent, align: right"]15:06
[/TD]
[TD="bgcolor: transparent, align: right"]259145
[/TD]
[TD="bgcolor: transparent, align: right"]259150
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]14:52
[/TD]
[TD="bgcolor: #dce6f1, align: right"]15:38
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259146
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259162
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]15:19
[/TD]
[TD="bgcolor: transparent, align: right"]15:42
[/TD]
[TD="bgcolor: transparent, align: right"]259155
[/TD]
[TD="bgcolor: transparent, align: right"]259163
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1, align: right"]15:27
[/TD]
[TD="bgcolor: #dce6f1, align: right"]15:47
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259157
[/TD]
[TD="bgcolor: #dce6f1, align: right"]259164
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent, align: right"]15:54
[/TD]
[TD="bgcolor: transparent, align: right"]16:02
[/TD]
[TD="bgcolor: transparent, align: right"]259165
[/TD]
[TD="bgcolor: transparent, align: right"]259167
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]


[TABLE="width: 635"]
<TBODY>[TR]
[TD="width: 847, bgcolor: transparent"]To make things less abstract:
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]The trip-value is the value of a trip meter in a car in kilometers.
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]So the top record actualy says:
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]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.




[TABLE="width: 635"]
<TBODY>[TR]
[TD="width: 847, bgcolor: transparent"]Now wat I'd like is to create a measure that transforms and divides this information like this:



[TABLE="class: grid, width: 193"]
<TBODY>[TR]
[TD="width: 62, bgcolor: #4f81bd"]ID
[/TD]
[TD="width: 80, bgcolor: #4f81bd"]binID
[/TD]
[TD="width: 115, bgcolor: #4f81bd"]distance travelled
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1"]0700-0800
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25
[/TD]
[TD="bgcolor: transparent"]0800-0900
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25
[/TD]
[TD="bgcolor: #dce6f1"]0900-1000
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent"]0700-0800
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]78
[/TD]
[TD="bgcolor: #dce6f1"]0800-0900
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]78
[/TD]
[TD="bgcolor: transparent"]0900-1000
[/TD]
[TD="bgcolor: #dce6f1"](value in kms)
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]


[/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="width: 341"]
<TBODY>[TR]
[TD="width: 455, bgcolor: transparent"]I have a table to relate to which contains binID, binstart and binstop
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]I also have a table for dates
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Is it even worth contemplating to solve this in Powerpivot, because it seems quite daunting to me atm.


[/TD]
[/TR]
</TBODY>[/TABLE]

 

[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):

[TABLE="width: 216"]
<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>[TR]
[TD="width: 122, bgcolor: transparent"]1) (raw data)[/TD]
[TD="width: 165, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]unrounded time A[/TD]
[TD="class: xl65, bgcolor: transparent"]trip value A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]unrounded time B[/TD]
[TD="class: xl65, bgcolor: transparent"]trip value B[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]2) (step 1 of transformation)[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]rounded time A[/TD]
[TD="class: xl65, bgcolor: transparent"]corrected trip value A[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]rounded time B[/TD]
[TD="class: xl65, bgcolor: transparent"]corrected trip value B[/TD]
[/TR]
</TBODY>[/TABLE]


[TABLE="width: 488"]
<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>[TR]
[TD="width: 293, bgcolor: transparent"]3) (step 2 of transformation)[/TD]
[TD="width: 357, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]from rounded time A to rounded time B[/TD]
[TD="class: xl66, bgcolor: transparent"]corrected trip value B minus corrected trip value A[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]equals:[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]distance travelled in time bin of rounded time (ie one hour)[/TD]
[/TR]
</TBODY>[/TABLE]

 

[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

[TABLE="class: cms_table_grid"]
<TBODY>[TR]
[TD="width: 21, bgcolor: #4f81bd"]ID[/TD]
[TD="width: 66, bgcolor: #4f81bd"]Timestart[/TD]
[TD="width: 65, bgcolor: #4f81bd"]Timestop[/TD]
[TD="width: 57, bgcolor: #4f81bd"]tripstart[/TD]
[TD="width: 56, bgcolor: #4f81bd"]tripstop[/TD]
[/TR]
[TR]
[TD="bgcolor: #dce6f1, align: right"]25[/TD]
[TD="bgcolor: #dce6f1, align: right"]6:55[/TD]
[TD="bgcolor: #dce6f1, align: right"]7:55[/TD]
[TD="bgcolor: #dce6f1, align: right"]259010[/TD]
[TD="bgcolor: #dce6f1, align: right"]259030[/TD]
[/TR]
</TBODY>[/TABLE]

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.