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
Count the votes:
[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 Row Number

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