## Transform to Compute Statistics

September 15th, 2016

There are some built-in transform classes to compute simple statistics.

Example: you want to find the maximum score across all player’s entries.

`[cfdb-table form="games" trans="MaxField(scores)"]`

This returns one row in your result like this:

score
(computed max)

Example: you want to find each person’s maximum score across a number of entries for each person’s name. This is like “max score grouped by name”

`[cfdb-table form="games" trans="MaxField(scores,name)"]`

This returns one row per unique name in your result with

name score
John 15
Richard 16

You can so the same with these classes or build your own:

• CountField
• SumField
• MinField
• MaxField
• AverageField

What if you want to “group by” more than one field?

Then create a new field that includes the information from all your fields, then “group by” it.

`[cfdb-table form="games" trans="full_name=concat(first_name,' ',last_name)&&MaxField(scores,full_name)"]`

name score
John Doe 15
Richard Roe 16

Notice the single-quoted space (‘ ‘) in “concat”. Single quote whitespace or it will be ignored.

Note: The “concat” function is provided by CFDB since there is no function for concatenation in PHP (it use the “.” operator).

## Counting Checkbox Values

In the case where you have a checkbox field with multiple selections, you may have data like this:

Vote
AA
AA
FF
CC,DD,EE
CC
GG,HH
`[cfdb-table form="voteform" trans="CountInField(Vote,Count)"]`
Vote Count
AA 2
FF 1
CC 2
DD 1
EE 1
GG 1
HH 1

To sort by the largest number of votes, append the NaturalSortByField transform

`[cfdb-table form="voteform" trans="CountInField(Vote,Count)&&NaturalSortByField(Count,DESC)"]`

If the values were separated with something other than a comma, such as “|” (e.g. “CC|DD|EE”) then indicate the delimiter:
`[cfdb-table form="voteform" trans="CountInField(Vote,Count,|)"]`

Add a column with a row number in it (as of version 2.10.10)

AddRowNumberField(fieldname,start) where “fieldname” is the column header and “start” (optional) is the number to start the count at (by default 1)

`[cfdb-table form="games" trans="AddRowNumberField(#)"]`

name score #
John 5 1
John 6 2
John 7 3
Richard 2 4

But that adds it at the end. You can move it to the beginning using “show”. Here we use start to indicate the “#” column followed by everything else:

`[cfdb-table form="games" show="#,/.*/" trans="AddRowNumberField(#)"]`

# name score
1 John 5
2 John 6
3 John 7
4 Richard 2

## Add a Summation Row at the Bottom of a Table

The SummationRow transform does this (as of version 2.9.12).

`[cfdb-table form="games" trans="SummationRow(score)"]`

name score
John 5
John 6
John 7
Richard 2
20

Sum Multiple Rows
`[cfdb-table form="games" trans="SummationRow(score1,score2)"]`

name score1 score2
John 5 4
John 6 7
John 7 9
Richard 2 1
20 21

Add label “Total” to in the Name column
`[cfdb-table form="games" trans="SummationRow(score1,score2,name:Total)"]`

name score1 score2
John 5 4
John 6 7
John 7 9
Richard 2 1
Total 20 21
Comments are closed.  Go To Support Forum