**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:

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:

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:

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 } ] [

moving-average ( [ distinct ] numeric_expr , numeric_expr [

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

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 } ] [

moving-total ( [ distinct ] numeric_expr , numeric_expr [

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 } ] [

percentage ( [ distinct ] numeric_expr [

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 } ] [

percentile ( [ distinct ] numeric_expr [

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 } ] [

quantile ( [ distinct ] numeric_expr, numeric_expr [

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 } ] [

quartile ( [ distinct ] numeric_expr [

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 } ] [

rank ( [ distinct ] expr [sort_order] {, expr [sort_order] } [

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 } ] [

running-average ( [ distinct ] numeric_expr [

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:

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 } ] [

running-count ( [ distinct ] numeric_expr [

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

Running-Difference:

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 } ] [

running-difference ( [ distinct ] numeric_expr [

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 } ] [

running-maximum ( [ distinct ] numeric_expr [

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 } ] [

running-minimum ( [ distinct ] numeric_expr [

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 } ] [

running-total ( [ distinct ] numeric_expr [

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:

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)

It was very nice article and it is very useful to Cognos learners.We also provide Cub training software online training.

ReplyDeleteGet your Free SEO report in 3 steps. We provide free and instant online reports

ReplyDeleteof webpage rankings in top 5 Search engines, Google, Bing, Yahoo.

Search Engine Optimization is the practice of improving the visibility (traffic)

of a web site, or page, in search engines organically.

Keyword ranking report