Writing Formulas in Factivate

Factivate provides users with a spreadsheet-like experience in order to minimize your programming/query needs. As a result, we support the majority of the formulas you already use in Excel, Google Sheets and many other spreadsheet programs. Additionally, we have created a new formula structure that will help you reference "Connected" datasheets in order to speed up your formula processing and automate calculations as new data is imported. 

These Datasheet formulas all maintain the same basic structure (examples are listed below)

FUNCTION(DS(SheetName!'Column':'Column'))
FUNCTION  - can mean any of the typical spreadsheet formula FUNCTIONS: SUM, AVERAGE, etc...
DS(                 - tells the spreadsheet to reference/recalc data from a datasheet. 
SheetName! - references the connected sheet name
'Column'       - references the column for which the function will take place. 

For examples on how to write these formulas, see below:

SUM(DS

Used to add values from Datasheet columns

Basic Formula
=SUM(DS(SheetName!'ColumnName':'ColumnName'))
Adding one Filter
=SUM(DS(SheetName!'ColumnName':'ColumnName', [@Column Name])
Adding multiple Filters with AND(
=SUM(DS(SheetName!'ColumnName':'ColumnName', AND([@Column Name]>=A1, [@Column Name]< =B1, [@Column Name]="text description")))

AVERAGE(DS

Used to average values from Datasheet columns

Basic Formula
=AVERAGE(DS(SheetName!'ColumnName':'ColumnName'))
Adding one Filter
=AVERAGE(DS(SheetName!'ColumnName':'ColumnName', [@Column Name])
Adding multiple Filters with AND(
=AVERAGE(DS(SheetName!'ColumnName':'ColumnName', AND([@Column Name]>=A1, [@Column Name]< =B1, [@Column Name]="text description")))

LARGE(DS

Used to list the top values from Datasheet columns sorted from high to low

Basic Formula to get top 3 (without filters)
=LARGE(DS(SheetName!'ColumnName':'ColumnName'), ,3)
Adding one Filter and sorted by [@Column Name] - (i.e., get top three posts sorted by Impressions)
=LARGE(DS(SheetName!'ColumnName':'ColumnName',AND([@Column Name]<A1), [@Column Name]),3)
Adding multiple Filters with AND( and sorted by [@Column Name]
=LARGE(DS(SheetName!'ColumnName':'ColumnName', AND([@Column Name]>=A1, [@Column Name]< =B1, [@Column Name]="text description"),[@Column Name]),3)

SMALL(DS

Used to list the bottom values from Datasheet columns sorted from low to high

Basic Formula to get top 3 (without filters)
=SMALL(DS(SheetName!'ColumnName':'ColumnName'), ,3)
Adding one Filter and sorted by [@Column Name] - (i.e., get bottom three posts sorted by Impressions)
=SMALL(DS(SheetName!'ColumnName':'ColumnName',AND([@Column Name]<A1), [@Column Name]),3)
Adding multiple Filters with AND( and sorted by [@Column Name]
=SMALL(DS(SheetName!'ColumnName':'ColumnName', AND([@Column Name]>=A1, [@Column Name]< =B1, [@Column Name]="text description"),[@Column Name]),3)

UNIQUE(DS

Used to list all of the unique values from a specified Datasheet column (i.e. list all posts published in January).

Basic Formula
=UNIQUE(DS(SheetName!'ColumnName':'ColumnName'))
Adding one Filter
=UNIQUE(DS(SheetName!'ColumnName':'ColumnName', [@Column Name]))
Adding multiple Filters with AND(
=UNIQUE(DS(SheetName!'ColumnName':'ColumnName', AND([@Column Name]>=A1, [@Column Name]< =B1, [@Column Name]="text description")))

COUNT(DS

Count how many times a value in a Datasheet columns appears

Basic Formula
=COUNT(DS(SheetName!'ColumnName':'ColumnName'))
Adding one Filter
=COUNT(DS(SheetName!'ColumnName':'ColumnName', [@Column Name])
Adding multiple Filters with AND(
=COUNT(DS(SheetName!'ColumnName':'ColumnName', AND([@Column Name]>=A1, [@Column Name]< =B1, [@Column Name]="text description")))

MIN(DS

Used to get the minimum value from a Datasheet column.

Basic Formula
=MIN(DS(SheetName!'ColumnName':'ColumnName'))
Adding one Filter
=MIN(DS(SheetName!'ColumnName':'ColumnName', [@Column Name])
Adding multiple Filters with AND(
=MIN(DS(SheetName!'ColumnName':'ColumnName', AND([@Column Name]>=A1, [@Column Name]< =B1, [@Column Name]="text description")))

MAX(DS

Used to get the maximum value from a Datasheet column.

Basic Formula
=MAX(DS(SheetName!'ColumnName':'ColumnName'))
Adding one Filter
=MAX(DS(SheetName!'ColumnName':'ColumnName', [@Column Name])
Adding multiple Filters with AND(
=MAX(DS(SheetName!'ColumnName':'ColumnName', AND([@Column Name]>=A1, [@Column Name]< =B1, [@Column Name]="text description")))