Built-in Functions → denseRank

denseRank() is an analytic function that returns the consecutive rank of each row based on the order of the grouped values. Rows with identical values share the same rank value. Subsequent rows increment the rank in consecutive sequence. For instance, if three rows have an identical rank value of N, then the subsequent row has a rank value of N + 1.

The following table illustrates the behavior of the denseRank() analytic function:

Category	Product	Min List Price	denseRank()
Fruit	Apple	0.25	1
Fruit	Orange	0.35	2
Fruit	Banana	0.40	3
Fruit	Lemon	0.40	3
Fruit	Kiwi	0.75	4
Fruit	Plum	0.75	4
Fruit	Apricot	0.75	4
Fruit	Yellow Melon	3.50	5
Fruit	Cantaloupe	3.50	5
Fruit	Pineapple	5.00	6

Note

Use the rank() function to return nonconsecutive rank values.

Signature

denseRank(groupBy, orderBy)

denseRank(groupBy(Dimension,...), orderBy(aggregation_function(measure), true/false,...))

The following table illustrates the denseRank() function parameters:

Parameter	Description
groupBy(Dimension,...)	Grouping function that specifies which group of data to operate over based on a dimension column, such as Product Category or Subcategory
orderBy()	Sorting function that specifies the order of rows based on an aggregation and a boolean value
aggregation_function(measure)	Aggregation function, such as min or max, that performs a calculation on a set of values for a measure, such as List Price or Cost, and returns a single value for each set
True	Enables an ascending order
False	Enables a descending order

Returns

int representing the consecutive rank of each value in the column.

Example

Find the consecutive rank of a product within a product category based on the minimum list price.

denseRank(
     groupBy(Prod_Category), 
     orderBy(
        min(list_price),
           true
            )
        )

Use the following steps for detailed instructions on how to use the denseRank() function to create the above query:

Note

In the Cluster Management Console (CMC), you can create a tenant that includes Sample Data. The Sample Data includes the SALES schema.

In the Navigation bar, select the Content tab, and then select + New → Add Dashboard.
In the Add Dashboard dialog, for Name, enter Product Dashboard, and then select Add.
In the Action bar, select + (add icon), or select + Add Insight.
In the Insight panel, select Listing Table or V.
In Tables, select Aggregated Table.
In the Data panel, select Add Data Set (+).
In the Manage Data Sets panel, in Tables, select SALES. Close the panel.
From the Data panel, drag and drop the following columns from the PRODUCTS table to the Grouping Dimension tray:
- Category
- Product
Drag and drop the List Price column from the PRODUCTS table to the Measure tray.
- If the Properties panel is not already open, select > to the right of the List Price pill to open the panel.
- In Format, select Dollar Rounded.
From the Data panel, drag and drop Add Formula to the Measure tray.
The Formula Builder automatically opens:
- In Search Functions, select the down arrow, and then select Analytic Functions.
- Double-click the denseRank function, denseRank(groupBy, orderBy), to add the formula to the editor.
- In the Formula Editor,
  - Replace dimension, ... with Category from the Data panel
  - Replace aggregation_function with min from the Functions panel
  - Replace measure with List Price from the Data panel
  - Replace true/false, ... with true

denseRank(groupBy(SALES.PRODUCTS.PROD_CATEGORY),orderBy(min(SALES.PRODUCTS.PROD_LIST_PRICE),true))

Select Validate & Save.
In the Measure tray, double-click the New Formula pill and rename it to Consecutive Rank (Min List Price).
Name the insight Product List Price and Consecutive Rank.
In the Action bar, select Save.

Content

Signature

Returns

Example