Cognos Summaries Functions

Aggregate:

Returns a calculated value using the appropriate aggregation function, based on the aggregation type of the expression.

Syntax: aggregate ( expr [ auto ] )
aggregate ( expr for [ all | any ] expr { , expr } )
aggregate ( expr for report )

Count:
Returns the number of selected data items excluding NULL values. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: count ( [ distinct ] expr [ auto ] )
count ( [ distinct ] expr for [ all | any ] expr { , expr } )
count ( [ distinct ] expr for report)

Example: count ([gosales_goretailers].[Products].[Product name]) - (o/p: 41, 15, 21)
Count ([Product type] for all [Product line]) ------------ (o/p: 5, 4, 4)
Count ([Product type] for any [Product line]) ----------- (o/p: 5, 4, 4)
Count ([Product type] for Report) ------------------- (o/p: 21 for all)


Maximum:
Returns the maximum value of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: maximum ( [ distinct ] expr [ auto ] )
maximum ( [ distinct ] expr for [ all | any ] expr { , expr } )
maximum ( [ distinct ] expr for report )

Example: maximum ([Quantity]) -------------------------------- (o/p: 962)
maximum ([Quantity] for all [gosales_goretailers].[Products].[Product type])
(o/p: 344,436…)
Maximum ([Quantity] for any [gosales_goretailers].[Products].[Product name])
(o/p: 44,78,….)
Maximum ([Quantity] for report) ------------------- (o/p: 1,646)


Minimum:
Returns the minimum value of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: minimum ( [ distinct ] expr [ auto ] )
minimum ( [ distinct ] expr for [ all | any ] expr { , expr } )
minimum ( [ distinct ] expr for report )

Example: minimum ([Quantity]) -------------- (o/p: 43,330, 142,150)
Minimum ([Quantity] for all [Product type]) ---- (o/p: 6, 4, 4, ….)
Minimum ([Quantity] for any [Product type]) – (o/p: 6, 4, 4...)
Minimum ([Quantity] for report) ----------------- (o/p: 2)

Moving-Average:
Returns the value by adding the values to the previous one based on the numeric_exp.

Syntax: moving-average ( numeric_expr , numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
moving-average ( [ distinct ] numeric_expr , numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: moving-average([Quantity],2)
(o/p:
Quantity Moving average
198,676 198,676
345,096 271,886
95,552 220,324
96,246 95,889


Moving-Total:
Returns the value by adding the values to the previous one based on the numeric_exp

Syntax: moving-total ( numeric_expr , numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
moving-total ( [ distinct ] numeric_expr , numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: moving-total([Quantity],3)
Output:Quantity Moving average
198,676 198,676
345,096 543,772
95,552 639,324
96,246 536,894


Percentage:
Returns the percent of the total value for selected data items. Only used in relational Data sources.

Syntax: percentage ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
percentage ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: percentage ([Quantity]) - (o/p: 16%, 12%, 10 %....)

Report: Percentage.

Percentile:
Returns a value, on a scale of one hundred, that indicates the percent of a distribution that is
equal to or below the selected data items.

Syntax: percentile ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
percentile ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: percentile ([Quantity]) -------- (o/p: 100%, 95%, 90%)

Quantile:
Returns the rank of a value for a range that you specify. It returns integers to represent any range of ranks, such as 1 (highest) to 100 (lowest).

Syntax: quantile ( numeric_expr, numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
quantile ( [ distinct ] numeric_expr, numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: quantile ([Quantity], 5) --- (o/p: 1, 2, 3, 4, 5)

Quartile:

Returns the rank of a value, represented as integers from 1 (highest) to 4 (lowest), relative to
a group of values.

Syntax: quartile ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
quartile ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: quartile ([Quantity]) ---------- (o/p: 1, 2, 3, 4)


Rank:

Returns the rank value of selected data items. If two or more rows tie, then there is a gap in the sequence of ranked values (also known as Olympic ranking).

Syntax: rank ( expr [sort_order] {, expr [sort_order] } [ at exp {, expr } ] [ ] [ prefilter ] )
rank ( [ distinct ] expr [sort_order] {, expr [sort_order] } [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: rank [Quantity]) ----------------- ( o/p: 1, 2, 3, 4, …..)

Running-Average:

Returns the running average by row (including the current row) for a set of values

Syntax: running-average ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-average ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-average [Quantity]) ------------ (o/p:
Quantity Moving average
198,676 198,676
345,096 271,886
95,552 213,108
96,246 183,892

Running-Count:
Returns the running count by row (including the current row) for a set of values.

Syntax: running-count ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-count ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-count([Quantity]) ------- (o/p: 1, 2, 3, … )


Running-Difference:

Returns a running difference by row, calculated as the difference between the value for the current row and the preceding row, (including the current row) for a set of values.

Syntax: running-difference ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-difference ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-difference ([Quantity]) --(o/p: null, 146420, -249,544, …..)


Running-Maximum:

Returns the running maximum by row (including the current row) for a set of values.

Syntax: running-maximum ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-maximum ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-maximum ([Quantity]) --------- (o/p: 198676, 345096)

Report: Running

Running-Minimum:

Returns the running minimum by row (including the current row) for a set of values.

Syntax: running-minimum ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-minimum ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-minimum ([Quantity]) --------- (o/p: 198676, 198676, 95552, )

Running-Total:

Returns a running total by row (including the current row) for a set of values.

Syntax: running-total ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-total ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-total ([Quantity]) ----------- (o/p: 198676, 543772….)

Standard-Deviation:

Returns the standard deviation of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: standard-deviation ( [ distinct ] expr [ auto ] )
standard-deviation ( [ distinct ] expr for [ all | any ] expr { , expr } )
standard-deviation ( [ distinct ] expr for report )

Example: standard-deviation ([Quantity]) ------ (o/p: 50.628, 69.098 …)
standard-deviation ([Quantity] for all [Product type]) -------- (o/p: 50.628, 69.098, …)
standard-deviation ([Quantity] for any [Product type]) ------ (o/p: 50.628, 69.098 …)
standard-deviation ([Quantity] for report) -------------------- (o/p: 59.70671027)

Report: Standard deviation

Standard-Deviation pop:
Computes the population standard deviation and returns the square root of the population variance. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: standard-deviation-pop ( [ distinct ] expr [ auto ] )
standard-deviation-pop ( [ distinct ] expr for [ all | any ] expr { , expr } )
standard-deviation-pop ( [ distinct ] expr for report )

Example: standard-deviation-pop ([Quantity]) ------- (o/p: 50.62240898, 69.09035128)
standard-deviation-pop ([Quantity] for all [Product type]) -
(o/p: 50.62240898, 69.09035128)
standard-deviation-pop ([Quantity] for any [Product type])
(o/p: 50.62240898, 69.09035128)
standard-deviation-pop ([Quantity] for report) ---
(o/p: 59.70601702, )


Total:
Returns the total value of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: total ( [ distinct ] expr [ auto ] )
total ( [ distinct ] expr for [ all | any ] expr { , expr } )
total ( [ distinct ] expr for report )

Example: total ([Quantity]) --------------- (o/p: 198,676, 345,096)
Total ([Quantity] for all [Product type]) ------- (o/p: 198,676, 345,096)
Total ([Quantity] for any [Product type]) ----- (o/p: 198,676, 345,096)
Total ([Quantity] for report) --------------- (o/p: 2215354)

Report: Total
Variance:
Returns the variance of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: variance ( [ distinct ] expr [ auto ] )
variance ( [ distinct ] expr for [ all | any ] expr { , expr } )
variance ( [ distinct ] expr for report )

Example: variance ([Quantity]) ----- (o/p: 2563.244, 4774.543…)
variance([Quantity] for all [Product type]) -------- (o/p: 2563.244, 4774.543…)
variance([Quantity] for any [Product type]) ------- (o/p: 2563.244, 4774.543…)
variance([Quantity] for report) ---------------------- (o/p: 3,564.89125097)


Variance-pop:

Returns the population variance of a set of numbers after discarding the nulls in this set.

Syntax: variance-pop ( [ distinct ] expr [ auto ] )
variance-pop ( [ distinct ] expr for [ all | any ] expr { , expr } )
variance-pop ( [ distinct ] expr for report )

Example: variance-pop([Quantity]) ------- (o/p: 2,562.6282905, 4,773.47663957)
variance-pop([Quantity] for all [Product type]) -----------
(o/p: 2,562.6282905, 4,773.47663957)
variance-pop([Quantity] for any [Product type]) --------
(o/p: 2,562.6282905, 4,773.47663957)
variance-pop([Quantity] for report) ------ (o/p: 3,564.80846781)