List of Dataset functions

Function name

Formula

Help Notes

AVERAGE

AVERAGE(value, *optional_grouping_value)

Calculate the average. If a second parameter is provided, create a sector average.

CONCATENATE  

CONCATENATE(value_field, grouping_field, *optional_separator, *include_self)

Return a string of the joined values of the field that share the value of the grouping field. The joined values are separated by a comma, or the value of *optional_seperator.

CONTAINS           

CONTAINS(value, search_for)

Returns the position of a search_for within the value. If 0 is returned it means search_for is not in your value.

COUNT 

COUNT(value, *optional_grouping_value)

Count the number that share this value. If a second parameter is provided, create a sector count.

DATE_FORMAT  

DATE_FORMAT(date_time, *optional_date_style)

Convert a date/time value to text. If no style is provided the British style is returned.

DISPLAY_NAME

DISPLAY_NAME()

Returns the current display name for the participant

DIVIDE  

DIVIDE(numerator, denominator)

Special form of divide which returns NULL instead of a divide-by-zero error when the denominator is 0.

FIRST_SUBMITTED

FIRST_SUBMITTED (*optional_instance_or_template_id, *optional_date_style)

The date the survey was first submitted.

FILES_WITH_DETAILS

FILES_WITH_DETAILS (question_description, node_id, template_or_instance_id)

Returns the details (Name, Description, Link) of file/image/file attachments associated with the survey question.

IF            

IF(your_test_is_true, use_this_value, *else_use_this_value)

Conditional calculations. If you leave out the optional else_use_this_value, nothing is returned when the condition is false.

IFBLANK               

IFBLANK(value_to_check, default_value)

Returns a default value if the value to check is blank (nothing).

IMPORT_TEXT   

IMPORT_TEXT(dataset_title, dataset_field)

Import a text value from another dataset.

IMPORT_VALUE 

IMPORT_VALUE(dataset_title, dataset_field)

Import a numeric value from another dataset

IS_MANAGER

IS_MANAGER()

Returns if user is manager

LAST_RESPONSE_ON

LAST_RESPONSE_ON (*optional_instance_or_template_id, *optional_date_style)

The date the survey was submitted.

LIST_AVERAGE

LIST_AVERAGE (<comma_separated_list_of_values>, <count_of_values_to_average_from>)

This function averages the comma separated values in first parameter based on the count entered in the second parameter.

MAX      

MAX(value, *optional_grouping_value)

Calculate the maximum. If a second parameter is provided, create a sector maximum.

MEDIAN

MEDIAN (value, count, position, *optional_grouping_value)

Returns the median value, but you need to have already calculated the COUNT and POSITION, which you then provide to this function.

MIN       

MIN(value, *optional_grouping_value)

Calculate the minimum. If a second parameter is provided, create a sector minimum.

MULTIPLY           

MULTIPLY(firstNumber, secondNumber)

Multiply first number with second number.

NTILE    

NTILE(number_of_tiles, value, *optional_grouping_value)

Calculate the "n-tile" position. First parameter is how many divisions to have. The second parameter is what to sort the rows by. If a third parameter is provided, create a sector grouping.

NUMBER_FORMAT

NUMBER_FORMAT(number)

Convert a number to text, comma-separating thousands.

OCCURANCE_ PERCENT             

OCCURANCE_PERCENT(value)

Calculate the percent of its occurance. The demical value will be rounded up.

OCCURANCE _PERCENT_SECTOR               

OCCURANCE_PERCENT_SECTOR (value,*optional_grouping_value)

Calculate the percent of its occurance by sector. The demical value will be rounded up.

PARTICIPANT_FIELD

PARTICIPANT_FIELD(field_index)

Returns the value stored in the participant table.

POSITION                           

POSITION(value, *optional_grouping_value)

The order this entry it compared to the other rows. (1, 2, 3,...)

QUARTILE           

QUARTILE(value, *optional_grouping_value)

Calculate the quartile position. If a second parameter is provided create a sector grouping.

QUESTION_TEXT

QUESTION_TEXT (question_description, node_id, instance_id)

Returns the text of particular node

QUINTILE            

QUINTILE(value, *optional_grouping_value)

Calculate the quintile position. If a second parameter is provided create a sector grouping.

RANK    

RANK(value, *optional_grouping_value)

The rank of this entry compared to the other rows. (1 for 1st, 2 for 2nd, 3 for 3rd,...)

RESPONSE          

RESPONSE(question_description, node_id, template_or_instance_id)

Returns the most recent response to the survey question.

ROUND                

ROUND(number, *number_of_digits)

Returns a number rounded (up or down - whichever is closest to zero) to the accuracy specified by *number_of_digits 

ROUNDDOWN   

ROUNDDOWN(number, *number_of_digits)

Returns a number rounded down (toward zero) to the accuracy specified by *number_of_digits

ROUNDUP           

ROUNDUP(number, *number_of_digits)

Returns a number rounded up (away from zero) to the accuracy specified by *number_of_digits

ROW

ROW(value, *optional_grouping_value)

The number of the row of this entry compared to the other rows. (1 for 1st, 2 for 2nd, 3 for 3rd,...)

SCORE   

SCORE(question_description, node_id, template_or_instance_id)

Returns the most recent score to the survey question.

STATUS_FLAGS

STATUS_FLAGS(question_description, node_id, template_or_instance_id)

Returns the most recent status flags to the survey question.

SUBMITTED        

SUBMITTED (*optional_instance_or_template_id, *optional_date_style)

The date the survey was submitted.

SUBSTR

SUBSTR(string,startindex,length)

It substring's the string provided from startindex(1 for first position) to the length.

SUM      

SUM(value, *optional_grouping_value)

Calculate the sum. If a second parameter is provided, create a sector sum.

SUM_SORT         

SORT(value1, value2, value3, value4, value5)

Returns the SUM of top 3 highest values. 

SUM_SORT_STRING

SUM_SORT_STRING(COUNT,VALUES)

This function will Sort the value and then add the number of values mentioned in <Count> parameter and return the SUM of them.

SURVEY_ID

SURVEY_ID()

The ID of the survey

SURVEY_LIST _AVERAGE_BASED_ ON_INDEXES

SURVEY_LIST_AVERAGE_BASED _ON_INDEXES (<comma separated list of values>,<comma separated indexes>)

This function is used to get the average of values from first parameter present at the indexes mentioned in the second parameter.

SURVEY_PROGRESS

SURVEY_PROGRESS(USER_ENTRY_ID)

Gives you survey completed percent

TEMPLATE_ID    

TEMPLATE_ID()

The ID of the survey template

TEXT      

TEXT(number)

Convert a number to text.

TEXT_SPLIT         

TEXT_SPLIT(text, split_character, number_to_return, *optional_join_character, *optional_skip_the_first_n)

Takes text, splits it using the split_character value (e.g. a comma, or a pipe ("|"))

USER_EMAILS

SURVEY_GET_LOGINEMAILS _FROM_PARTICIPANT_ID()

This function is used to get the email id's of all logins for particular participants as comma seperated values.

USER_ENTRY_ID

USER_ENTRY_ID(instance_id)

Get the user_entry_id for a specific survey instance.

VALUE  

VALUE(text)

Convert text to a number.

WHOLE_SCORE

WHOLE_SCORE (question_description, node_id, template_or_instance_id)

Returns the score of the checkbox and radio only