System Development, Software Updates, Quick Guides and Builder Training InformationBuilder Site Help Documentation Calculations and Table CalculationsCalculations: Greater than and less than, and Nested If calculations (for use with GPS fields and HR fields)

# Calculations: Greater than and less than, and Nested If calculations (for use with GPS fields and HR fields)

## How to set up the greater than and lesser than calcs.

Note: All of these will return "Answer" as I've set them up. The first number in the brackets is the query field and the second number is the limit we are interested in

Abbreviations

gt = greater than

gteq = greater than or equal to

lt = less than

lteq = less than or equal to

## Examples of different equations used to calculate Time and Distance parameters in a GPS analysis Event Form

All of the Equations are listed here to ensure you can quickly copy and use them in your own Time Series Forms.

FIELD NAME              FIELD TYPE            CALCULATION                                                            -OUTCOME/REASON

Drill time                   Calculation                (End time  Start time)/60/1000                                -To give you time of each drill in minutes

Start Distance       Table Calculation ,        Aggregate: minifpositive if(gteq(GPS.time,Start time), GPS.distance,-1)

-Distance at start of drill

End Distance             Table Calculation,         Aggregate: maxifpositive if(lteq(GPS.time,End time), GPS.distance,-1) -Distance at end of drill

Drill Distance                Calculation           safe( End Distance -  Start Distance)                         -Distance for the drill

Drill Workrate                 Calculation           safe( Drill Distance/Drill time)                                    -Workrate for the drill

Max Speed (Drill)     Table Calculation       Aggregate: maxifpositive if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), GPS.speed,-1),-1)      -Max speed reached in this drill

Distance < X            Table Calculation,           Aggregate: sumifpositive safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(lteq(GPS.speed,X),GPS.distance.delta,-1),-1),-1))

-Distance covered at speeds lower than X km/h

Time <X           Table Calculation             Aggregate: sumifpositive (safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(lteq(GPS.speed,X),GPS.time.delta,-1),-1),-1)))/1000/60

-Time spent at speeds lower than X km/h

Distance X to Y  Table Calculation             Aggregate: sumifpositive safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(gt(GPS.speed,X), if(lteq(GPS.speed,Y),GPS.distance.delta,-1),-1),-1),-1))

-Distance covered between speeds of X and Y km/h. use this formula for all other speed zones

Time X to Y       Table Calculation,            Aggregate: sumifpositive (safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(gt(GPS.speed,X), if(lteq(GPS.speed,Y),GPS.time.delta,-1),-1),-1),-1)))/1000/60

-Time spent between speeds of X and Y km/hr. use this formula for all other speed zones

Distance < 50% MRS Table Calculation     Aggregate: sumifpositive safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(lteq(GPS.speed,(0.5 * Max Sprint Speed)),GPS.distance.delta,-1),-1),-1))

-Distance covered at speeds less than 50% of the athletes individual Maximal Sprint Speed. Use the same formula for TIME  just replace GPS.distance.delta with GPS.time.delta.

Distance 50 to 75 %MRS Table Calculation,     Aggregate: sumifpositive safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(gt(GPS.speed,(0.5*Max Sprint Speed)), if(lteq(GPS.speed,(0.75*Max Sprint Speed)),GPS.distance.delta,-1),-1),-1),-1))

-Distance covered at speeds between 50 and 75% of MRS. Use this formula for any other relative speed zones. Use the same formula for TIME  just replace GPS.distance.delta with GPS.time.delta.

Distance > 75%MRS     Table Calculation,           Aggregate: sumifpositive safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(gt(GPS.speed,(0.75* Max Sprint Speed)),GPS.distance.delta,-1),-1),-1))

-Distance covered at speeds greater than 75% of MRS.

TIME use the same formula for TIME  just replace GPS.distance.delta with GPS.time.delta.

## Translating one of the nested IF statements. What they mean.

AGG: sumifpositive

Formula:  safe(if(gteq(GPS.time,Start time), if(lteq(GPS.time,End time), if(gt(GPS.speed,X), if(lteq(GPS.speed,Y),GPS.distance.delta,-1),-1),-1),-1))

What this means

If the time in this row in the raw upload file is greater than or equal to the start time of this drill, AND less than or equal to the end time of this drill, AND if the speed is greater than x km/h, AND the speed is less than Y km/h, THEN give me the distance delta value. If any rows in the file don’t meet all four IF statements then give them a -1 value. This is a table calculation, so go to the next row and do it all again. And keep doing it until you have looked at every row in the file. You will end up with some rows which met all four criteria and some that didn’t. Because I selected the aggregate to be SUMIFPOSITIVE I want you to take all those values that meet the IF statements and add them together to give me the total distance for this drill. SAFE means if all of this ends up giving me a number show me that number, if not, show me a zero, but just don’t give me a blank (because blanks cannot be used in calculations).

The above table of formulas is only concerned with time, distance and speed variables. The same formulas can be used (slightly modify them to reference the columns you want) to perform HR calculations such as average HR in this drill, Max HR in this Drill, etc. if your GPS units export accelerometer data you can also use the same logic to create calculations to provide summary values of acceleration data.

NOTE: the more fields you want the longer this table will be and it will not display nicely in the application.

## Examples of Summary Calculation for the a whole session (e.g. total distance and total time)

Once you have filtered and processed the raw file in a Table you can add in summary calculations for the session. Examples of these are outlined here:

FIELD NAME                    FIELD TYPE                CALCULATION

Total Distance               Table Calculation        Aggregate: SUM Drill Distance

Total Time                         Table Calculation         Aggregate: SUM Drill Time

Max Speed                    Table Calculation         Aggregate: MAX Max Speed (Drill)

Total Distance < X           Table Calculation         Aggregate: SUM Distance <X

Total Distance X to Y      Table Calculation        Aggregate: SUM Distance X to Y

Total Time <X                Table Calculation         Aggregate: SUM Time <X

Total Time X to Y           Table Calculation       Aggregate: SUM Time X to Y

Total Distance 50 to 75%MRS   Table Calculation        Aggregate: SUM Distance 50 to 75%MRS

Total Time 50 to 75% MRS         Table Calculation        Aggregate: SUM Time 50 to 75% MRS

Repeat as necessary for all absolute and relative speed zones.

If you are collecting HR, data as well you can include additional fields for the HR analysis.