AggregateOffset ( field ; type ; offset )

Aggregate one field from the newest records of a set (FMP18+ only)

Be the first to rate this function Log in to vote

Menno van Beek - Show more from this author
Van Beek Zakelijke Software
https://www.vbzs.nl

Sample input:

Aggregate ( MyTOC::MyNumberField ; "Sum" ; 3 )

Contenst of MyTOC::MyNumberField:
10
12
99
16
11
32
45
19

Sample output:

101

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*
###################### 
# AggregateOffset function 
# Format: AggregateOffset ( field ; type ; offset ) 
# 
# Parameters:
# field: the field you wish to aggregate (must be a number except for 
# "count" and "list")
# type: Average/Count/List/Max/Min/StDev/StDevP/Sum/Variance/VarianceP
# offset: offset from last value 
# 
# Data returned: 
# A aggregated number calculated from the offset of records of a found set
# 
# Description: 
# Aggregate the designated number of records with one of the standard 
# functions for aggregation in FM. The size set is the offset of the 
# tailrecords of the found set. Thus if 100 records are found and the size 
# is set to 20, the first 80 records will be ignored
# 
# Author:
# Menno van Beek - Van Beek Zakelijke Software - Woerden, the Netherlands
# 
# September 01, 2021
###################### 
*/

While ( [ 

	f = get ( FoundCount ) ; 
	r = "" ; 
	i = 0

] ; 

	i < size 

; [ 
 
	v = GetNthRecord ( field ; f - size + i ) ; 
	r = List ( r ; v ) ; 
	i = i + 1

] ; 

	Evaluate ( type & "(" & Substitute ( r ; ¶ ; ";" ) & ")" )

)

Aggregate the designated number of tailrecords for a field with one of the standard functions for aggregation in FM. The size set is to offset the tailrecords of the found set. Thus if 100 records are found and the size is set to 20, the first 80 records will be ignored.

You can use the standard names of the FM aggregation-function like: Average, Count, List, Max, Min, StDev, StDevP, Sum,Variance and VarianceP

This can be handy when you don't want to do a find or narrow your current set of records

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

AggregateOffset ( field ; type ; offset )

Comments

Support this website.

Support this website.