# Table calculation functions


When you are analyzing data in a specific visual, you can apply table calculations to the current set of data to discover how dimensions influence measures or each other. *Visualized data* is your result set based on your current dataset, with all the filters, field selections, and customizations applied. To see exactly what this result set is, you can export your visual to a file. A *table calculation function* performs operations on the data to reveal relationships between fields. 

In this section, you can find a list of the functions available in table calculations that you can perform on visualized data in Amazon Quick. 

To view a list of functions sorted by category, with brief definitions, see [Functions by category](https://docs.amazonaws.cn/quicksight/latest/user/functions-by-category.html). 

**Topics**
+ [

# difference
](difference-function.md)
+ [

# distinctCountOver
](distinctCountOver-function.md)
+ [

# lag
](lag-function.md)
+ [

# lead
](lead-function.md)
+ [

# percentDifference
](percentDifference-function.md)
+ [

# avgOver
](avgOver-function.md)
+ [

# countOver
](countOver-function.md)
+ [

# maxOver
](maxOver-function.md)
+ [

# minOver
](minOver-function.md)
+ [

# percentileOver
](percentileOver-function.md)
+ [

# percentileContOver
](percentileContOver-function.md)
+ [

# percentileDiscOver
](percentileDiscOver-function.md)
+ [

# percentOfTotal
](percentOfTotal-function.md)
+ [

# periodOverPeriodDifference
](periodOverPeriodDifference-function.md)
+ [

# periodOverPeriodLastValue
](periodOverPeriodLastValue-function.md)
+ [

# periodOverPeriodPercentDifference
](periodOverPeriodPercentDifference-function.md)
+ [

# periodToDateAvgOverTime
](periodToDateAvgOverTime-function.md)
+ [

# periodToDateCountOverTime
](periodToDateCountOverTime-function.md)
+ [

# periodToDateMaxOverTime
](periodToDateMaxOverTime-function.md)
+ [

# periodToDateMinOverTime
](periodToDateMinOverTime-function.md)
+ [

# periodToDateSumOverTime
](periodToDateSumOverTime-function.md)
+ [

# stdevOver
](stdevOver-function.md)
+ [

# stdevpOver
](stdevpOver-function.md)
+ [

# varOver
](varOver-function.md)
+ [

# varpOver
](varpOver-function.md)
+ [

# sumOver
](sumOver-function.md)
+ [

# denseRank
](denseRank-function.md)
+ [

# rank
](rank-function.md)
+ [

# percentileRank
](percentileRank-function.md)
+ [

# runningAvg
](runningAvg-function.md)
+ [

# runningCount
](runningCount-function.md)
+ [

# runningMax
](runningMax-function.md)
+ [

# runningMin
](runningMin-function.md)
+ [

# runningSum
](runningSum-function.md)
+ [

# firstValue
](firstValue-function.md)
+ [

# lastValue
](lastValue-function.md)
+ [

# windowAvg
](windowAvg-function.md)
+ [

# windowCount
](windowCount-function.md)
+ [

# windowMax
](windowMax-function.md)
+ [

# windowMin
](windowMin-function.md)
+ [

# windowSum
](windowSum-function.md)

# difference


The `difference` function calculates the difference between a measure based on one set of partitions and sorts, and a measure based on another. 

The `difference` function is supported for use with analyses based on SPICE and direct query data sets.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
difference
	(
	     measure 
	     ,[ sortorder_field ASC_or_DESC, ... ]
	     ,lookup_index,
	     ,[ partition field, ... ] 
	)
```

## Arguments


 *measure*   
An aggregated measure that you want to see the difference for. 

 *sort order field*   
One or more measures and dimensions that you want to sort the data by, separated by commas. You can specify either ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *lookup index*   
The lookup index can be positive or negative, indicating a following row in the sort (positive) or a previous row in the sort (negative). The lookup index can be 1–2,147,483,647. For the engines MySQL, MariaDB and Aurora MySQL-Compatible Edition, the lookup index is limited to just 1.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates the difference between of `sum({Billed Amount})`, sorted by `Customer Region` ascending, compared to the next row, and partitioned by `Service Line`.

```
difference(
     sum( {Billed Amount} ), 
     [{Customer Region} ASC],
     1,
     [{Service Line}]
)
```

The following example calculates the difference between `Billed Amount` compared to the next line, partitioned by (`[{Customer Region}]`). The fields in the table calculation are in the field wells of the visual.

```
difference(
     sum( {Billed Amount} ), 
     [{Customer Region} ASC],
     1
)
```

The red highlights show how each amount is added ( a \$1 b = c ) to show the difference between amounts a and c. 

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/differenceCalc.png)


# distinctCountOver


The `distinctCountOver` function calculates the distinct count of the operand partitioned by the specified attributes at a specified level. Supported levels are `PRE_FILTER` and `PRE_AGG`. The operand must be unaggregated.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
distinctCountOver
(
  measure or dimension field 
  ,[ partition_field, ... ]  
  ,calculation level 
)
```

## Arguments


 *measure or dimension field*   
The measure or dimension that you want to do the calculation for, for example `{Sales Amt}`. Valid values are `PRE_FILTER` and `PRE_AGG`.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
This value defaults to `POST_AGG_FILTER` when blank. `POST_AGG_FILTER` is not a valid level for this operation and will result in an error message. For more information, see [Using level-aware calculations in Amazon Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example gets the distinct count of `Sales` partitioned over `City` and `State` at the `PRE_AGG` level.

```
distinctCountOver
(
  Sales, 
  [City, State], PRE_AGG
)
```

# lag


The `lag` function calculates the lag (previous) value for a measure based on specified partitions and sorts.

`lag` is supported for use with analyses based on SPICE and direct query data sets.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
lag
(
lag
(
 measure
 ,[ sortorder_field ASC_or_DESC, ... ] 
 ,lookup_index
 ,[ partition_field, ... ] 
)] 
)
```

## Arguments


*measure*   
The measure that you want to get the lag for. This can include an aggregate, for example `sum({Sales Amt})`.

*sort order field*   
One or more measures and dimensions that you want to sort the data by, separated by commas. You can specify either ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

*lookup index*   
The lookup index can be positive or negative, indicating a following row in the sort (positive) or a previous row in the sort (negative). The lookup index can be 1–2,147,483,647. For the engines MySQL, MariaDB, and Amazon Aurora MySQL-Compatible Edition, the lookup index is limited to just 1.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates the previous `sum(sales)`, partitioned by the state of origin, in the ascending sort order on `cancellation_code`.

```
lag
(
     sum(Sales), 
     [cancellation_code ASC], 
     1, 
     [origin_state_nm]
)
```

The following example uses a calculated field with `lag` to display sales amount for the previous row next to the amount for the current row, sorted by `Order Date`. The fields in the table calculation are in the field wells of the visual.

```
lag(
     sum({Sales}),
     [{Order Date} ASC],
     1
)
```

The following screenshot shows the results of the example.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/lagCalc.png)


The following example uses a calculated field with `lag` to display the sales amount for the previous row next to the amount for the current row, sorted by `Order Date` partitioned by `Segment`.

```
lag
	(
		sum(Sales),
		[Order Date ASC],
		1, [Segment]
	)
```

The following screenshot shows the results of the example.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/lagCalc2.png)


# lead


The `lead` function calculates the lead (following) value for a measure based on specified partitions and sorts.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
lead
(
     measure
     ,[ sortorder_field ASC_or_DESC, ... ]  
     ,lookup_index,
     ,[ partition_field, ... ]
)
```

## Arguments


*measure*   
The measure that you want to get the lead for. This can include an aggregate, for example `sum({Sales Amt})`.

*sort order field*   
One or more measures and dimensions that you want to sort the data by, separated by commas. You can specify either ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

*lookup index*   
The lookup index can be positive or negative, indicating a following row in the sort (positive) or a previous row in the sort (negative). The lookup index can be 1–2,147,483,647. For the engines MySQL, MariaDB, and Amazon Aurora MySQL-Compatible Edition, the lookup index is limited to just 1.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates the next `sum(sales)`, partitioned by the state of origin, in the ascending sort order on `cancellation_code`.

```
lead
(
     sum(sales), 
     [cancellation_code ASC], 
     1, 
     [origin_state_nm]
)
```

The following example uses a calculated field with lead to display the amount for the next row beside the amount for the current row, sorted by `Customer Segment`. The fields in the table calculation are in the field wells of the visual.

```
lead(
     sum({Billed Amount}),
     [{Customer Segment} ASC],
     1
)
```

The following screenshot shows the results of the example.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/leadCalc.png)


# percentDifference


The `percentDifference` function calculates the percentage difference between the current value and a comparison value, based on partitions, sorts, and lookup index. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
percentDifference
(
  measure 
  ,[ sortorder_field ASC_or_DESC, ... ]  
  ,lookup index
  ,[ partition_field, ... ] 
)
```

## Arguments


 *measure*   
An aggregated measure that you want to see the percent difference for. 

 *sort order field*   
One or more measures and dimensions that you want to sort the data by, separated by commas. You can specify either ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *lookup index*   
The lookup index can be positive or negative, indicating a following row in the sort (positive) or a previous row in the sort (negative). The lookup index can be 1–2,147,483,647. For the engines MySQL, MariaDB and Aurora MySQL-Compatible Edition, the lookup index is limited to just 1.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates the percentage of difference between the `sum(Sales)` for the current and the previous `State`, sorted by `Sales`.

```
percentDifference
(
  sum(amount), 
  [sum(amount) ASC],
  -1, 
  [State]
)
```

The following example calculates the percent that a specific `Billed Amount` is of another `Billed Amount`, sorted by (`[{Customer Region} ASC]`). The fields in the table calculation are in the field wells of the visual.

```
percentDifference
(
  sum( {Billed Amount} ), 
  [{Customer Region} ASC],
  1
)
```

The following screenshot shows the results of the example. The red letters show that the total `Billed Amount` for the `Customer Region` **APAC** is 24 percent less than the amount for the **EMEA** region.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/percentDifference.png)


# avgOver


The `avgOver` function calculates the average of a measure partitioned by a list of dimensions. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
avgOver
(
     measure 
     ,[ partition_field, ... ] 
     ,calculation level 
)
```

The following example shows the average `Billed Amount` over `Customer Region`. The fields in the table calculation are in the field wells of the visual.

```
avgOver
(
     sum({Billed Amount}),
     [{Customer Region}]
)
```

The following screenshot shows the results of the example. With the addition of `Service Line`, the total amount billed for each is displayed, and the average of these three values displays in the calculated field.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/avgOver.png)


## Arguments


 *measure*   
The measure that you want to do the calculation for, for example `sum({Sales Amt})`. Use an aggregation if the calculation level is set to `NULL` or `POST_AGG_FILTER`. Don't use an aggregation if the calculation level is set to `PRE_FILTER` or `PRE_AGG`.

 *partition field*  
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (Default) Table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example gets the average `sum(Sales)` partitioned over `City` and `State`. 

```
avgOver
(
     sum(Sales), 
     [City, State]
)
```

# countOver


The `countOver` function calculates the count of a dimension or measure partitioned by a list of dimensions. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
countOver
(
  measure or dimension field 
  ,[ partition_field, ... ]  
  ,calculation level 
)
```

## Arguments


 *measure or dimension field*   
The measure or dimension that you want to do the calculation for, for example `sum({Sales Amt})`. Use an aggregation if the calculation level is set to `NULL` or `POST_AGG_FILTER`. Don't use an aggregation if the calculation level is set to `PRE_FILTER` or `PRE_AGG`.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (Default) Table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example gets the count of `Sales` partitioned over `City` and `State`. 

```
countOver
(
  Sales, 
  [City, State]
)
```

The following example gets the count of `{County}` partitioned over `City` and `State`. 

```
countOver
(
  {County}, 
  [City, State]
)
```

The following example shows the count of `Billed Amount` over `Customer Region`. The fields in the table calculation are in the field wells of the visual.

```
countOver
(
  sum({Billed Amount}),
  [{Customer Region}]
)
```

The following screenshot shows the results of the example. Because there are no other fields involved, the count is one for each region.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/countOver1.png)


If you add additional fields, the count changes. In the following screenshot, we add `Customer Segment` and `Service Line`. Each of those fields contains three unique values. With 3 segments, 3 service lines, and 3 regions, the calculated field shows 9.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/countOver2.png)


If you add the two additional fields to the partitioning fields in the calculated field, `countOver( sum({Billed Amount}), [{Customer Region}, {Customer Segment}, {Service Line}]`, then the count is again 1 for each row.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/countOver.png)


# maxOver


The `maxOver` function calculates the maximum of a measure or date partitioned by a list of dimensions. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
maxOver
(
     measure 
     ,[ partition_field, ... ] 
     ,calculation level 
)
```

## Arguments


 *measure*   
The measure that you want to do the calculation for, for example `sum({Sales Amt})`. Use an aggregation if the calculation level is set to `NULL` or `POST_AGG_FILTER`. Don't use an aggregation if the calculation level is set to `PRE_FILTER` or `PRE_AGG`.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (Default) Table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example calculates the maximum `sum(Sales)`, partitioned by `City` and `State`.

```
maxOver
(
     sum(Sales), 
     [City, State]
)
```

The following example shows the maximum `Billed Amount` over `Customer Region`. The fields in the table calculation are in the field wells of the visual.

```
maxOver
(
     sum({Billed Amount}),
     [{Customer Region}]
)
```

The following screenshot shows the results of the example. With the addition of `Service Line`, the total amount billed for each is displayed, and the maximum of these three values displays in the calculated field.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/maxOver.png)


# minOver


The `minOver` function calculates the minimum of a measure or date partitioned by a list of dimensions. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
minOver
(
     measure 
     ,[ partition_field, ... ] 
     ,calculation level 
)
```

## Arguments


*measure*   
The measure that you want to do the calculation for, for example `sum({Sales Amt})`. Use an aggregation if the calculation level is set to `NULL` or `POST_AGG_FILTER`. Don't use an aggregation if the calculation level is set to `PRE_FILTER` or `PRE_AGG`.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (Default) Table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example calculates the min `sum(Sales)`, partitioned by `City` and `State`.

```
minOver
(     
     sum(Sales), 
     [City, State]
)
```

The following example shows the minimum `Billed Amount` over `Customer Region`. The fields in the table calculation are in the field wells of the visual.

```
minOver
(
     sum({Billed Amount}),
     [{Customer Region}]
)
```

The following screenshot shows the results of the example. With the addition of `Service Line`, the total amount billed for each is displayed, and the minimum of these three values displays in the calculated field.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/minOver.png)


# percentileOver


The `percentileOver` function calculates the *n*th percentile of a measure partitioned by a list of dimensions. There are two varieties of the `percentileOver` calculation available in Quick:
+ [https://docs.amazonaws.cn/quicksight/latest/user/percentileContOver-function.html](https://docs.amazonaws.cn/quicksight/latest/user/percentileContOver-function.html) uses linear interpolation to determine result.
+ [https://docs.amazonaws.cn/quicksight/latest/user/percentileDiscOver-function.html](https://docs.amazonaws.cn/quicksight/latest/user/percentileDiscOver-function.html) uses actual values to determine result. 

The `percentileOver` function is an alias of `percentileDiscOver`.

# percentileContOver


The `percentileContOver` function calculates the percentile based on the actual numbers in `measure`. It uses the grouping and sorting that are applied in the field wells. The result is partitioned by the specified dimension at the specified calculation level. 

Use this function to answer the following question: Which actual data points are present in this percentile? To return the nearest percentile value that is present in your dataset, use `percentileDiscOver`. To return an exact percentile value that might not be present in your dataset, use `percentileContOver` instead. 

## Syntax


```
percentileContOver (
    measure
  , percentile-n
  , [partition-by, …]
  , calculation-level
)
```

## Arguments


 *measure*   
Specifies a numeric value to use to compute the percentile. The argument must be a measure or metric. Nulls are ignored in the calculation. 

 *percentile-n*   
The percentile value can be any numeric constant 0–100. A percentile value of 50 computes the median value of the measure. 

 *partition-by*   
(Optional) One or more dimensions that you want to partition by, separated by commas. Each field in the list is enclosed in \$1 \$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation-level*   
 Specifies where to perform the calculation in relation to the order of evaluation. There are three supported calculation levels:  
+ PRE\$1FILTER
+ PRE\$1AGG
+ POST\$1AGG\$1FILTER (default) – To use this calculation level, specify an aggregation on `measure`, for example `sum(measure)`.
PRE\$1FILTER and PRE\$1AGG are applied before the aggregation occurs in a visualization. For these two calculation levels, you can't specify an aggregation on `measure` in the calculated field expression. To learn more about calculation levels and when they apply, see [Order of evaluation in Amazon Quick](https://docs.amazonaws.cn/quicksight/latest/user/order-of-evaluation-quicksight.html) and [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Returns


The result of the function is a number. 

## Example of percentileContOver
Example

The following example helps explain how percentileContOver works.

**Example Comparing calculation levels for the median**  
The following example shows the median for a dimension (category) by using different calculation levels with the `percentileContOver` function. The percentile is 50. The dataset is filtered by a region field. The code for each calculated field is as follows:  
+ `example = left( category, 1 )` (A simplified example.)
+ `pre_agg = percentileContOver ( {Revenue} , 50 , [ example ] , PRE_AGG)`
+ `pre_filter = percentileContOver ( {Revenue} , 50 , [ example ] , PRE_FILTER) `
+ `post_agg_filter = percentileContOver ( sum ( {Revenue} ) , 50 , [ example ], POST_AGG_FILTER )`

```
example   pre_filter     pre_agg      post_agg_filter
------------------------------------------------------
0            106,728     119,667            4,117,579
1            102,898      95,946            2,307,547
2             97,807      93,963              554,570  
3            101,043     112,585            2,709,057
4             96,533      99,214            3,598,358
5            106,293      97,296            1,875,648
6             97,118      69,159            1,320,672
7            100,201      90,557              969,807
```

# percentileDiscOver


The `percentileDiscOver` function calculates the percentile based on the actual numbers in `measure`. It uses the grouping and sorting that are applied in the field wells. The result is partitioned by the specified dimension at the specified calculation level. The `percentileOver` function is an alias of `percentileDiscOver`.

Use this function to answer the following question: Which actual data points are present in this percentile? To return the nearest percentile value that is present in your dataset, use `percentileDiscOver`. To return an exact percentile value that might not be present in your dataset, use `percentileContOver` instead. 

## Syntax


```
percentileDiscOver (
     measure
   , percentile-n
   , [partition-by, …]
   , calculation-level
)
```

## Arguments


 *measure*   
Specifies a numeric value to use to compute the percentile. The argument must be a measure or metric. Nulls are ignored in the calculation. 

 *percentile-n*   
The percentile value can be any numeric constant 0–100. A percentile value of 50 computes the median value of the measure. 

 *partition-by*   
(Optional) One or more dimensions that you want to partition by, separated by commas. Each field in the list is enclosed in \$1 \$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation-level*   
 Specifies where to perform the calculation in relation to the order of evaluation. There are three supported calculation levels:  
+ PRE\$1FILTER
+ PRE\$1AGG
+ POST\$1AGG\$1FILTER (default) – To use this calculation level, you need to specify an aggregation on `measure`, for example `sum(measure)`.
PRE\$1FILTER and PRE\$1AGG are applied before the aggregation occurs in a visualization. For these two calculation levels, you can't specify an aggregation on `measure` in the calculated field expression. To learn more about calculation levels and when they apply, see [Order of evaluation in Amazon Quick](https://docs.amazonaws.cn/quicksight/latest/user/order-of-evaluation-quicksight.html) and [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Returns


The result of the function is a number. 

## Example of percentileDiscOver
Example

The following example helps explain how percentileDiscOver works.

**Example Comparing calculation levels for the median**  
The following example shows the median for a dimension (category) by using different calculation levels with the `percentileDiscOver` function. The percentile is 50. The dataset is filtered by a region field. The code for each calculated field is as follows:  
+ `example = left( category, 1 )` (A simplified example.)
+ `pre_agg = percentileDiscOver ( {Revenue} , 50 , [ example ] , PRE_AGG)`
+ `pre_filter = percentileDiscOver ( {Revenue} , 50 , [ example ] , PRE_FILTER) `
+ `post_agg_filter = percentileDiscOver ( sum ( {Revenue} ) , 50 , [ example ], POST_AGG_FILTER )`

```
example   pre_filter     pre_agg      post_agg_filter
------------------------------------------------------
0            106,728     119,667            4,117,579
1            102,898      95,946            2,307,547
2             97,629      92,046              554,570  
3            100,867     112,585            2,709,057
4             96,416      96,649            3,598,358
5            106,293      97,296            1,875,648
6             97,118      64,395            1,320,672
7             99,915      90,557              969,807
```

**Example The median**  
The following example calculates the median (the 50th percentile) of `Sales` partitioned by `City` and `State`.   

```
percentileDiscOver
(
  Sales, 
  50,
  [City, State]
)
```
The following example calculates the 98th percentile of `sum({Billed Amount})` partitioned by `Customer Region`. The fields in the table calculation are in the field wells of the visual.  

```
percentileDiscOver
(
  sum({Billed Amount}), 
  98,
  [{Customer Region}]
)
```
The following screenshot shows the how these two examples look on a chart.   

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/percentilOver-50-98.png)


# percentOfTotal


The `percentOfTotal` function calculates the percentage a measure contributes to the total, based on the dimensions specified. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
percentOfTotal
(
     measure 
     ,[ partition_field, ... ] 
)
```

## Arguments


 *measure*   
An aggregated measure that you want to see the percent of total for. Currently, the `distinct count` aggregation is not supported for `percentOfTotal`.

 *partition field*  
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example creates a calculation for the percent of total `Sales` contributed by each `State`.

```
percentOfTotal
(
     sum(Sales), 
     [State]
)
```

The following example calculates the percent that a specific `Billed Amount` is when compared to the total `Billed Amount`, partitioned by (`[{Service Line} ASC]`). The fields in the table calculation are in the field wells of the visual.

```
percentOfTotal
(
     sum( {Billed Amount} ), 
     [{Service Line}]
)
```

The following screenshot shows the results of the example. The red highlights show that the partition field with the value "`Billing`" has three entries, one for each region. The total billed amount for this service line is divided into three percentages, which total 100 percent. Percentages are rounded and might not always add up to exactly 100 percent.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/percentOfTotal.png)


# periodOverPeriodDifference


The `periodOverPeriodDifference` function calculates the difference of a measure over two different time periods as specified by period granularity and offset. Unlike a difference calculation, this function uses a date-based offset instead of a fixed sized offset. This ensures that only the correct dates are compared, even if data points are missing in the dataset.

## Syntax


```
periodOverPeriodDifference(
	measure, 
	date, 
	period, 
	offset)
```

## Arguments


 *measure*   
An aggregated measure that you want to perform the periodOverPeriod calculation on.

 *dateTime*   
The Date dimension over which we are computing Period-Over-Period calculations.

 *period*   
(Optional) The time period across which you're computing the computation. Granularity of `YEAR` means `YearToDate` computation, `Quarter` means `QuarterToDate`, and so on. Valid granularities include `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `HOUR`, `MINUTE`, and `SECONDS`.  
The defaults value is the visual date dimension granularity.

 *offset*   
(Optional) The offset can be a positive or negative integer representing the prior time period (specified by period) that you want to compare against. For instance, period of a quarter with offset 1 means comparing against the previous quarter.  
The default value is 1.

## Example


The following example uses a calculated field `PeriodOverPeriod` to display the sales amount difference of yesterday

```
periodOverPeriodDifference(sum(Sales), {Order Date})
```

![\[This is an image of the return of the example calculation.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/MonthOverMonthDifference.png)


The following example uses a calculated field `PeriodOverPeriod` to display the sales amount difference of previous 2 months. Below example is comparing sales of `Mar2020` with `Jan2020`.

```
periodOverPeriodDifference(sum(Sales),{Order Date}, MONTH, 1)
```

![\[This is an image of the return of the example calculation.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/MonthOverMonthDifference2.png)


# periodOverPeriodLastValue


The `periodOverPeriodLastValue` function calculates the last (previous) value of a measure from the previous time period as specified by the period granularity and offset. This function uses a date-based offset instead of a fixed sized offset. This ensures only the correct dates are compared, even if data points are missing in the dataset.

## Syntax


```
periodOverPeriodLastValue(
	measure, 
	date, 
	period, 
	offset)
```

## Arguments


 *measure*   
An aggregated measure that you want to see the difference for.

 *date*   
The date dimension over which you're computing periodOverPeriod calculations.

 *period*   
(Optional) The time period across which you're computing the computation. Granularity of `YEAR` means `YearToDate` computation, `Quarter` means `QuarterToDate`, and so on. Valid granularities include `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `HOUR`, `MINUTE`, and `SECONDS`.  
This argument defaults to the granularity of the visual aggregation

 *offset*   
(Optional) The offset can a positive or negative integer representing the prior time period (specified by period) that you want to compare against. For instance, period of a quarter with offset 1 means comparing against the previous quarter.  
This argument default value is 1.

## Example


The following example calculates the month over month value in sales with the visual dimension granularity and default offset of 1.

```
periodOverPeriodLastValue(sum(Sales), {Order Date})
```

The following example calculates the month over month value in sales with a fixed granularity of `MONTH` and fixed offset of 1.

```
periodOverPeriodLastValue(sum(Sales), {Order Date},MONTH, 1)
```

![\[This is an image of the return of the example calculation.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/MonthOverMonthLastValue.png)


# periodOverPeriodPercentDifference


The `periodOverPeriodPercentDifference` function calculates the percent difference of a measure over two different time periods as specified by the period granularity and offset. Unlike percentDifference, this function uses a date-based offset instead of a fixed sized offset. This ensures only the correct dates are compared, even if data points are missing in the dataset.

## Syntax


```
periodOverPeriodPercentDifference(
	measure, 
	date, 
	period, 
	offset)
```

## Arguments


 *measure*   
An aggregated measure that you want to see the difference for.

 *date*   
The date dimension over which you're computing periodOverPeriod calculations.

 *period*   
(Optional) The time period across which you're computing the computation. Granularity of `YEAR` means `YearToDate` computation, `Quarter` means `QuarterToDate`, and so on. Valid granularities include `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `HOUR`, `MINUTE`, and `SECONDS`.  
This argument defaults to the granularity of the visual aggregation

 *offset*   
(Optional) The offset can a positive or negative integer representing the prior time period (specified by period) that you want to compare against. For instance, period of a quarter with offset 1 means comparing against the previous quarter.  
This argument default value is 1.

## Example


The following example calculates the month over month percent difference in sales with the visual dimension granularity and default offset of 1.

```
periodOverPeriodPercentDifference(sum(Sales),{Order Date})
```

The following example calculates the month over month percent difference in sales with a fixed granularity of `MONTH` and fixed offset of 1.

```
periodOverPeriodPercentDifference(sum(Sales), {Order Date}, MONTH, 1)
```

![\[This is an image of the return of the example calculation.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/MonthOverMonthPercentDifference.png)


# periodToDateAvgOverTime


The `periodToDateAvgOverTime` function calculates the average of a measure for a given time granularity (for instance, a quarter) up to a point in time.

## Syntax


```
periodToDateAvgOverTime(
	measure, 
	dateTime,
	period)
```

## Arguments


 *measure*   
An aggregated measure that you want to do the calculation

 *dateTime*   
The date dimension over which you're computing PeriodOverTime calculations.

 *period*   
(Optional) The time period across which you're computing the computation. Granularity of `YEAR` means `YearToDate` computation, `Quarter` means `QuarterToDate`, and so on. Valid granularities include `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `HOUR`, `MINUTE`, and `SECONDS`.  
The default value is the visual's date dimension granularity.

## Example


The following function calculates the average fare amount month over mont.

```
periodToDateAvgOverTime(sum({fare_amount}), pickupDatetime, MONTH)
```

![\[This is an image of the results of the example calculation with illustrations.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/PTDAvgOverTimeResults.png)


# periodToDateCountOverTime


The `periodToDateCountOverTime` function calculates the count of a dimension or measure for a given time granularity (for instance, a quarter) up to a point in time.

## Syntax


```
periodToDateCountOverTime(
	measure, 
	dateTime, 
	period)
```

## Arguments


 *measure*   
An aggregated measure that you want to do the calculation

 *dateTime*   
The date dimension over which you're computing PeriodOverTime calculations.

 *period*   
(Optional) The time period across which you're computing the computation. Granularity of `YEAR` means `YearToDate` computation, `Quarter` means `QuarterToDate`, and so on. Valid granularities include `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `HOUR`, `MINUTE`, and `SECONDS`.  
The default value is the visual's date dimension granularity.

## Example


The following example calculates the count of vendors month over month.

```
periodToDateCountOverTime(count(vendorid), pickupDatetime, MONTH)
```

![\[This is an image of the results of the example calculation with illustrations.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/PTDCountOverTimeResults.png)


# periodToDateMaxOverTime


The `periodToDateMaxOverTime` function calculates the maximum of a measure for a given time granularity (for instance, a quarter) up to a point in time.

## Syntax


```
periodToDateMaxOverTime(
	measure, 
	dateTime, 
	period)
```

## Arguments


 *measure*   
An aggregated measure that you want to do the calculation

 *dateTime*   
The date dimension over which you're computing PeriodOverTime calculations.

 *period*   
(Optional) The time period across which you're computing the computation. Granularity of `YEAR` means `YearToDate` computation, `Quarter` means `QuarterToDate`, and so on. Valid granularities include `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `HOUR`, `MINUTE`, and `SECONDS`.  
The default value is the visual's date dimension granularity.

## Example


The following example calculates the maximum fare amount month over month.

```
periodToDatemaxOverTime(max({fare_amount}), pickupDatetime, MONTH)
```

![\[This is an image of the results of the example calculation with illustrations.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/PTDMaxOverTimeResults.png)


# periodToDateMinOverTime


The `periodToDateMinOverTime` function calculates the minimum of a measure for a given time granularity (for instance, a quarter) up to a point in time.

## Syntax


```
periodToDateMinOverTime(
	measure, 
	dateTime, 
	period)
```

## Arguments


 *measure*   
An aggregated measure that you want to do the calculation

 *dateTime*   
The date dimension over which you're computing PeriodOverTime calculations.

 *period*   
(Optional) The time period across which you're computing the computation. Granularity of `YEAR` means `YearToDate` computation, `Quarter` means `QuarterToDate`, and so on. Valid granularities include `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `HOUR`, `MINUTE`, and `SECONDS`.  
The default value is the visual's date dimension granularity.

## Example


The following example calculates the minimum fare amount month over month.

```
periodToDateMinOverTime(min({fare_amount}), pickupDatetime, MONTH)
```

![\[This is an image of the results of the example calculation with illustrations.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/PTDMinOverTimeResults.png)


# periodToDateSumOverTime


The `periodToDateSumOverTime` function calculates the sum of a measure for a given time granularity (for instance, a quarter) up to a point in time.

## Syntax


```
periodToDateSumOverTime(
	measure, 
	dateTime, 
	period)
```

## Arguments


 *measure*   
An aggregated measure that you want to do the calculation

 *dateTime*   
The date dimension over which you're computing PeriodOverTime calculations.

 *period*   
(Optional) The time period across which you're computing the computation. Granularity of `YEAR` means `YearToDate` computation, `Quarter` means `QuarterToDate`, and so on. Valid granularities include `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, `HOUR`, `MINUTE`, and `SECONDS`.  
The default value is the visual's date dimension granularity.

## Example


The following function returns the total fare amount month over month.

```
periodToDateSumOverTime(sum({fare_amount}), pickupDatetime, MONTH)
```

![\[This is an image of the results of the example calculation with illustrations.\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/PTDSumOverTime-example-results.png)


# stdevOver


The `stdevOver` function calculates the standard deviation of the specified measure, partitioned by the chosen attribute or attributes, based on a sample. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
stdevOver
(
      measure 
     ,[ partition_field, ... ] 
     ,calculation level 
)
```

## Arguments


*measure*   
The measure that you want to do the calculation for, for example `sum({Sales Amt})`. Use an aggregation if the calculation level is set to `NULL` or `POST_AGG_FILTER`. Don't use an aggregation if the calculation level is set to `PRE_FILTER` or `PRE_AGG`.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (default) table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example calculates the standard deviation of `sum(Sales)`, partitioned by `City` and `State`, based on a sample..

```
stdevOver
(
     sum(Sales), 
     [City, State]
)
```

The following example calculates the standard deviation of `Billed Amount` over `Customer Region`, based on a sample. The fields in the table calculation are in the field wells of the visual.

```
stdevOver
(
     sum({Billed Amount}),
     [{Customer Region}]
)
```

# stdevpOver


The `stdevpOver` function calculates the standard deviation of the specified measure, partitioned by the chosen attribute or attributes, based on a biased population.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
stdevpOver
(
     measure 
     ,[ partition_field, ... ] 
     ,calculation level 
)
```

## Arguments


*measure*   
The measure that you want to do the calculation for, for example `sum({Sales Amt})`. Use an aggregation if the calculation level is set to `NULL` or `POST_AGG_FILTER`. Don't use an aggregation if the calculation level is set to `PRE_FILTER` or `PRE_AGG`.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (default) table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example calculates the standard deviation of `sum(Sales)`, partitioned by `City` and `State`, based on a biased population.

```
stdevpOver
(
     sum(Sales), 
     [City, State]
)
```

The following example calculates the standard deviation of `Billed Amount` over `Customer Region`, based on a biased population. The fields in the table calculation are in the field wells of the visual.

```
stdevpOver
(
     sum({Billed Amount}),
     [{Customer Region}]
)
```

# varOver


The `varOver` function calculates the variance of the specified measure, partitioned by the chosen attribute or attributes, based on a sample. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
varOver
(
     measure 
     ,[ partition_field, ... ] 
     ,calculation level 
)
```

## Arguments


*measure*   
The measure that you want to do the calculation for, for example `sum({Sales Amt})`. Use an aggregation if the calculation level is set to `NULL` or `POST_AGG_FILTER`. Don't use an aggregation if the calculation level is set to `PRE_FILTER` or `PRE_AGG`.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (Default) Table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example calculates the variance of `sum(Sales)`, partitioned by `City` and `State`, based on a sample.

```
varOver
(
     sum(Sales), 
     [City, State]
)
```

The following example calculates the variance of `Billed Amount` over `Customer Region`, based on a sample. The fields in the table calculation are in the field wells of the visual.

```
varOver
(
     sum({Billed Amount}),
     [{Customer Region}]
)
```

# varpOver


The `varpOver` function calculates the variance of the specified measure, partitioned by the chosen attribute or attributes, based on a biased population. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
varpOver
(
     measure 
     ,[ partition_field, ... ] 
     ,calculation level 
)
```

## Arguments


*measure*   
The measure that you want to do the calculation for, for example `sum({Sales Amt})`. Use an aggregation if the calculation level is set to `NULL` or `POST_AGG_FILTER`. Don't use an aggregation if the calculation level is set to `PRE_FILTER` or `PRE_AGG`.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (Default) Table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example calculates the variance of `sum(Sales)`, partitioned by `City` and `State`, based on a biased population.

```
varpOver
(
     sum(Sales), 
     [City, State]
)
```

The following example calculates the variance of `Billed Amount` over `Customer Region`, based on a biased population. The fields in the table calculation are in the field wells of the visual.

```
varpOver
(
     sum({Billed Amount}),
     [{Customer Region}]
)
```

# sumOver


 The `sumOver` function calculates the sum of a measure partitioned by a list of dimensions. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
sumOver
(
     measure 
     ,[ partition_field, ... ] 
     ,calculation level 
)
```

## Arguments


*measure*   
The measure that you want to do the calculation for, for example `sum({Sales Amt})`. Use an aggregation if the calculation level is set to `NULL` or `POST_AGG_FILTER`. Don't use an aggregation if the calculation level is set to `PRE_FILTER` or `PRE_AGG`.

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (default) table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example calculates the sum of `sum(Sales)`, partitioned by `City` and `State`.

```
sumOver
(
     sum(Sales), 
     [City, State]
)
```

The following example sums `Billed Amount` over `Customer Region`. The fields in the table calculation are in the field wells of the visual.

```
sumOver
(
     sum({Billed Amount}),
     [{Customer Region}]
)
```

The following screenshot shows the results of the example. With the addition of `Customer Segment`, the total amount billed for each is summed for the `Customer Region`, and displays in the calculated field.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/sumOver.png)


# denseRank


The `denseRank` function calculates the rank of a measure or a dimension in comparison to the specified partitions. It counts each item only once, ignoring duplicates, and assigns a rank "without holes" so that duplicate values share the same rank. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
denseRank
(
  [ sort_order_field ASC_or_DESC, ... ] 
  ,[ partition_field, ... ] 
)
```

## Arguments


 *sort order field*   
One or more aggregated fields, either measures or dimensions or both, that you want to sort the data by, separated by commas. You can either specify ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (Default) Table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example densely ranks `max(Sales)`, based on a descending sort order, by `State` and `City`. Any cities with the same `max(Sales)` are assigned the same rank, and the next city is ranked consecutively after them. For example, if three cities share the same ranking, the fourth city is ranked as second. 

```
denseRank
(
  [max(Sales) DESC], 
  [State, City]
)
```

The following example densely ranks `max(Sales)`, based on a descending sort order, by `State`. Any states with the same `max(Sales)` are assigned the same rank, and the next is ranked consecutively after them. For example, if three states share the same ranking, the fourth state is ranked as second. 

```
denseRank
(
  [max(Sales) DESC], 
  [State]
)
```

# rank


The `rank` function calculates the rank of a measure or a dimension in comparison to the specified partitions. It counts each item, even duplicates, once and assigns a rank "with holes" to make up for duplicate values. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
rank
(
  [ sort_order_field ASC_or_DESC, ... ]
  ,[ partition_field, ... ] 
)
```

## Arguments


 *sort order field*   
One or more aggregated measures and dimensions that you want to sort the data by, separated by commas. You can specify either ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (Default) Table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example ranks `max(Sales)`, based on a descending sort order, by `State` and `City`, within the `State` of **WA**. Any cities with the same `max(Sales)` are assigned the same rank, but the next rank includes the count of all previously existing ranks. For example, if three cities share the same ranking, the fourth city is ranked as fourth. 

```
rank
(
  [max(Sales) DESC], 
  [State, City]
)
```

The following example ranks `max(Sales)`, based on an ascending sort order, by `State`. Any states with the same `max(Sales)` are assigned the same rank, but the next rank includes the count of all previously existing ranks. For example, if three states share the same ranking, the fourth state is ranked as fourth. 

```
rank
(
  [max(Sales) ASC], 
  [State]
)
```

The following example ranks `Customer Region` by total `Billed Amount`. The fields in the table calculation are in the field wells of the visual.

```
rank(
  [sum({Billed Amount}) DESC]
)
```

The following screenshot shows the results of the example, along with the total `Billed Amount` so you can see how each region ranks.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/rankCalc.png)


# percentileRank


The `percentileRank` function calculates the percentile rank of a measure or a dimension in comparison to the specified partitions. The percentile rank value(*x*) indicates that the current item is above *x*% of values in the specified partition. The percentile rank value ranges from 0 (inclusive) to 100 (exclusive). 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
percentileRank
(
      [ sort_order_field ASC_or_DESC, ... ] 
     ,[ {partition_field}, ... ]
)
```

## Arguments


 *sort order field*   
One or more aggregated measures and dimensions that you want to sort the data by, separated by commas. You can specify either ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *calculation level*  
(Optional) Specifies the calculation level to use:  
+ **`PRE_FILTER`** – Prefilter calculations are computed before the dataset filters.
+ **`PRE_AGG`** – Preaggregate calculations are computed before applying aggregations and top and bottom *N* filters to the visuals.
+ **`POST_AGG_FILTER`** – (Default) Table calculations are computed when the visuals display. 
This value defaults to `POST_AGG_FILTER` when blank. For more information, see [Using level-aware calculations in Quick](https://docs.amazonaws.cn/quicksight/latest/user/level-aware-calculations.html).

## Example


The following example does a percentile ranking of `max(Sales)` in descending order, by `State`. 

```
percentileRank
(
     [max(Sales) DESC], 
     [State]
)
```

The following example does a percentile ranking of `Customer Region` by total `Billed Amount`. The fields in the table calculation are in the field wells of the visual.

```
percentileRank(
     [sum({Billed Amount}) DESC],
     [{Customer Region}]
)
```

The following screenshot shows the results of the example, along with the total `Billed Amount` so you can see how each region compares.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/percentileRank.png)


# runningAvg


The `runningAvg` function calculates a running average for a measure based on the specified dimensions and sort orders. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions. 

```
runningAvg
(
  measure 
  ,[ sortorder_field ASC_or_DESC, ... ]  
  ,[ partition_field, ... ] 
)
```

## Arguments


 *measure*   
An aggregated measure that you want to see the running average for. 

 *sort order field*   
One or more measures and dimensions that you want to sort the data by, separated by commas. You can specify either ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *partition field*  
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates a running average of `sum(Sales)`, sorted by `Sales`, partitioned by `City` and `State`.

```
runningAvg
(
  sum(Sales), 
  [Sales ASC], 
  [City, State]
)
```

The following example calculates a running average of `Billed Amount`, sorted by month (`[truncDate("MM",Date) ASC]`). The fields in the table calculation are in the field wells of the visual.

```
runningAvg
(
  sum({Billed Amount}),
  [truncDate("MM",Date) ASC]
)
```

# runningCount


The `runningCount` function calculates a running count for a measure or dimension, based on the specified dimensions and sort orders. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions. 

```
runningCount
(
  measure_or_dimension 
  ,[ sortorder_field ASC_or_DESC, ... ]  
  ,[ partition_field, ... ] 
)
```

## Arguments


 *measure or dimension*   
An aggregated measure or dimension that you want to see the running count for. 

 *sort order field*   
One or more measures and dimensions that you want to sort the data by, separated by commas. You can specify either ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *partition field*  
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates a running count of `sum(Sales)`, sorted by `Sales`, partitioned by `City` and `State`.

```
runningCount
(
  sum(Sales), 
  [Sales ASC], 
  [City, State]
)
```

The following example calculates a running count of `Billed Amount`, sorted by month (`[truncDate("MM",Date) ASC]`). The fields in the table calculation are in the field wells of the visual.

```
runningCount
(
  sum({Billed Amount}),
  [truncDate("MM",Date) ASC]
)
```

# runningMax


The `runningMax` function calculates a running maximum for a measure based on the specified dimensions and sort orders. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions. 

```
runningMax
(
  measure 
  ,[ sortorder_field ASC_or_DESC, ... ]  
  ,[ partition_field, ... ] 
)
```

## Arguments


 *measure*   
An aggregated measure that you want to see the running maximum for. 

 *sort order field*   
One or more measures and dimensions that you want to sort the data by, separated by commas. You can specify either ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *partition field*  
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates a running maximum of `sum(Sales)`, sorted by `Sales`, partitioned by `City` and `State`.

```
runningMax
(
  sum(Sales), 
  [Sales ASC], 
  [City, State]
)
```

The following example calculates a running maximum of `Billed Amount`, sorted by month (`[truncDate("MM",Date) ASC]`). The fields in the table calculation are in the field wells of the visual.

```
runningMax
(
  sum({Billed Amount}),
  [truncDate("MM",Date) ASC]
)
```

# runningMin


The `runningMin` function calculates a running minimum for a measure based on the specified dimensions and sort orders. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions. 

```
runningMin
(
  measure 
  ,[ sortorder_field ASC_or_DESC, ... ]  
  ,[ partition_field, ... ] 
)
```

## Arguments


 *measure*   
An aggregated measure that you want to see the running minimum for. 

 *sort order field*   
One or more measures and dimensions that you want to sort the data by, separated by commas. You can specify either ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *partition field*  
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates a running minimum of `sum(Sales)`, sorted by `Sales`, partitioned by `City` and `State`.

```
runningMin
(
  sum(Sales), 
  [Sales ASC], 
  [City, State]
)
```

The following example calculates a running minimum of `Billed Amount`, sorted by month (`[truncDate("MM",Date) ASC]`). The fields in the table calculation are in the field wells of the visual.

```
runningMin
(
  sum({Billed Amount}),
  [truncDate("MM",Date) ASC]
)
```

# runningSum


The `runningSum` function calculates a running sum for a measure based on the specified dimensions and sort orders. 

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions. 

```
runningSum
(
  measure 
  ,[ sortorder_field ASC_or_DESC, ... ]  
  ,[ partition_field, ... ] 
)
```

## Arguments


 *measure*   
An aggregated measure that you want to see the running sum for. 

 *sort order field*   
One or more measures and dimensions that you want to sort the data by, separated by commas. You can specify either ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

 *partition field*  
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates a running sum of `sum(Sales)`, sorted by `Sales`, partitioned by `City` and `State`.

```
runningSum
(
  sum(Sales), 
  [Sales ASC], 
  [City, State]
)
```

The following example calculates a running sum of `Billed Amount`, sorted by month (`[truncDate("MM",Date) ASC]`). The fields in the table calculation are in the field wells of the visual.

```
runningSum
(
  sum({Billed Amount}),
  [truncDate("MM",Date) ASC]
)
```

The following screenshot shows the results of the example. The red labels show how each amount is added ( `a + b = c` ) to the next amount, resulting in a new total. 

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/runningSum.png)


# firstValue


The `firstValue` function calculates the first value of the aggregated measure or dimension partitioned and sorted by specified attributes.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
firstValue
	(
	     aggregated measure or dimension, 
	     [ sort_attribute ASC_or_DESC, ... ],
	     [ partition_by_attribute, ... ] 
	)
```

## Arguments


*aggregated measure or dimension*   
An aggregated measure or dimension that you want to see the first value for.

*sort attribute*   
One or more aggregated fields, either measures or dimensions or both, that you want to sort the data by, separated by commas. You can either specify ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it's more than one word. The entire list is enclosed in [ ] (square brackets).

*partition by attribute*  
(Optional) One or more measure or dimensions that you want to partition by, separated by commas.  
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets). 

## Example


The following example calculates the first `Destination Airport`, sorted by `Flight Date`, partitioned by `Flight Date` ascending and `Origin Airport`.

```
firstValue(
    {Destination Airport}
    [{Flight Date} ASC],
    [
        {Origin Airport},
        {Flight Date}
    ]
)
```

# lastValue


The `lastValue` function calculates the last value of the aggregated measure or dimension partitioned and sorted by specified attributes.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
lastValue
	(
	     aggregated measure or dimension,
	     [ sort_attribute ASC_or_DESC, ... ],
	     [ partition_by_attribute, ... ] 
	)
```

## Arguments


*aggregated measure or dimension*   
An aggregated measure or dimension that you want to see the last value for.

*sort attribute*   
One or more aggregated fields, either measures or dimensions or both, that you want to sort the data by, separated by commas. You can either specify ascending (`ASC`) or descending (`DESC`) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it's more than one word. The entire list is enclosed in [ ] (square brackets).

*partition by attribute*  
(Optional) One or more measures or dimensions that you want to partition by, separated by commas.  
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets). 

## Example


The following example calculates the last value for `Destination Airport`. This calculation is sorted by the `Flight Date` value and partitioned by the `Flight Date` value sorted in ascending order and the `Origin Airport` value.

```
lastValue(
    [{Destination Airport}],
    [{Flight Date} ASC],
    [
        {Origin Airport},
    	truncDate('DAY', {Flight Date})
    ]
)
```

# windowAvg


The `windowAvg` function calculates the average of the aggregated measure in a custom window that is partitioned and sorted by specified attributes. Usually, you use custom window functions on a time series, where your visual shows a metric and a date field. For example, you can use `windowAvg` to calculate a moving average, which is often used to smooth out the noise in a line chart.

Window functions aren't supported for MySQL versions earlier than 8 and MariaDB versions earlier than 10.2.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
windowAvg
	(
	     measure 
            , [sort_order_field ASC/DESC, ...]
            , start_index
            , end_index
	     ,[ partition_field, ... ] 
	)
```

## Arguments


*measure*   
The aggregated metric that you want to get the average for, for example `sum({Revenue})`.

*sort attribute*   
One or more aggregated fields, either measures or dimensions or both, that you want to sort the data by, separated by commas. You can either specify ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1 \$1 (curly braces), if it's more than one word. The entire list is enclosed in [ ] (square brackets).

*start index*   
The start index is a positive integer, indicating *n* rows above the current row. The start index counts the available data points above the current row, rather than counting actual time periods. If your data is sparse (missing months or years, for example), adjust the indexes accordingly. 

*end index*   
The end index is a positive integer, indicating *n* rows below the current row. The end index counts the available data points below the current row, rather than counting actual time periods. If your data is sparse (missing months or years, for example), adjust the indexes accordingly. 

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it's more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates the moving average of `sum(Revenue)`, partitioned by `SaleDate`. The calculation includes three rows above and two row below of the current row.

```
windowAvg
	(
	     sum(Revenue), 
	     [SaleDate ASC],
	     3,
            2
	)
```

The following screenshot shows the results of this moving average example. The sum(Revenue) field is added to the chart to show the difference between the revenue and the moving average of revenue.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/windowAvg.png)


# windowCount


The `windowCount` function calculates the count of the aggregated measure or dimension in a custom window that is partitioned and sorted by specified attributes. Usually, you use custom window functions on a time series, where your visual shows a metric and a date field.

Window functions aren't supported for MySQL versions earlier than 8 and MariaDB versions earlier than 10.2.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
windowCount
	(
	     measure_or_dimension 
            , [sort_order_field ASC/DESC, ...]
            , start_index
            , end_index
	     ,[ partition_field, ... ] 
	)
```

## Arguments


*measure or dimension*   
The aggregated metric that you want to get the average for, for example `sum({Revenue})`.

*sort attribute*   
One or more aggregated fields, either measures or dimensions or both, that you want to sort the data by, separated by commas. You can either specify ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it's more than one word. The entire list is enclosed in [ ] (square brackets).

*start index*   
The start index is a positive integer, indicating *n* rows above the current row. The start index counts the available data points above the current row, rather than counting actual time periods. If your data is sparse (missing months or years, for example), adjust the indexes accordingly. 

*end index*   
The end index is a positive integer, indicating *n* rows below the current row. The end index counts the available data points below the current row, rather than counting actual time periods. If your data is sparse (missing months or years, for example), adjust the indexes accordingly. 

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it's more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates the moving count of `sum(Revenue)`, partitioned by `SaleDate`. The calculation includes three rows above and two row below of the current row.

```
windowCount
	(
	     sum(Revenue), 
	     [SaleDate ASC],
	     3,
               2
	)
```

# windowMax


The `windowMax` function calculates the maximum of the aggregated measure in a custom window that is partitioned and sorted by specified attributes. Usually, you use custom window functions on a time series, where your visual shows a metric and a date field. You can use `windowMax` to help you identify the maximum of the metric over a period time.

Window functions aren't supported for MySQL versions earlier than 8 and MariaDB versions earlier than 10.2.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
windowMax
	(
	     measure 
            , [sort_order_field ASC/DESC, ...]
            , start_index
            , end_index
	     ,[ partition_field, ... ] 
	)
```

## Arguments


*measure*   
The aggregated metric that you want to get the average for, for example `sum({Revenue})`.

*sort attribute*   
One or more aggregated fields, either measures or dimensions or both, that you want to sort the data by, separated by commas. You can either specify ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it's more than one word. The entire list is enclosed in [ ] (square brackets).

*start index*   
The start index is a positive integer, indicating *n* rows above the current row. The start index counts the available data points above the current row, rather than counting actual time periods. If your data is sparse (missing months or years, for example), adjust the indexes accordingly. 

*end index*   
The end index is a positive integer, indicating *n* rows below the current row. The end index counts the available data points below the current row, rather than counting actual time periods. If your data is sparse (missing months or years, for example), adjust the indexes accordingly. 

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it is more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates the trailing 12-month maximum of `sum(Revenue)`, partitioned by `SaleDate`. The calculation includes 12 rows above and 0 row below of the current row.

```
windowMax
	(
	     sum(Revenue), 
	     [SaleDate ASC],
	     12,
               0
	)
```

The following screenshot shows the results of this trailing 12-month example. The sum(Revenue) field is added to the chart to show the difference between the revenue and the trailing 12-month maximum revenue.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/windowMax.png)


# windowMin


The `windowMin` function calculates the minimum of the aggregated measure in a custom window that is partitioned and sorted by specified attributes. Usually, you use custom window functions on a time series, where your visual shows a metric and a date field. You can use `windowMin` to help you identify the minimum of the metric over a period time.

Window functions aren't supported for MySQL versions earlier than 8 and MariaDB versions earlier than 10.2.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
windowMin
	(
	     measure 
            , [sort_order_field ASC/DESC, ...]
            , start_index
            , end_index
	     ,[ partition_field, ... ] 
	)
```

## Arguments


*measure*   
The aggregated metric that you want to get the average for, for example `sum({Revenue})`.

*sort attribute*   
One or more aggregated fields, either measures or dimensions or both, that you want to sort the data by, separated by commas. You can either specify ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it's more than one word. The entire list is enclosed in [ ] (square brackets).

*start index*   
The start index is a positive integer, indicating *n* rows above the current row. The start index counts the available data points above the current row, rather than counting actual time periods. If your data is sparse (missing months or years, for example), adjust the indexes accordingly. 

*end index*   
The end index is a positive integer, indicating *n* rows below the current row. The end index counts the available data points below the current row, rather than counting actual time periods. If your data is sparse (missing months or years, for example), adjust the indexes accordingly. 

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it's more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates the trailing 12-month minimum of `sum(Revenue)`, partitioned by `SaleDate`. The calculation includes 12 rows above and 0 row below of the current row.

```
windowMin
	(
	     sum(Revenue), 
	     [SaleDate ASC],
	     12,
               0
	)
```

The following screenshot shows the results of this trailing 12-month example. The sum(Revenue) field is added to the chart to show the difference between the revenue and the trailing 12-month minimum revenue.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/windowMin.png)


# windowSum


The `windowSum` function calculates the sum of the aggregated measure in a custom window that is partitioned and sorted by specified attributes. Usually, you use custom window functions on a time series, where your visual shows a metric and a date field. 

Window functions aren't supported for MySQL versions earlier than 8 and MariaDB versions earlier than 10.2.

## Syntax


The brackets are required. To see which arguments are optional, see the following descriptions.

```
windowSum
	(
	     measure 
            , [sort_order_field ASC/DESC, ...]
            , start_index
            , end_index
	     ,[ partition_field, ... ] 
	)
```

## Arguments


*measure*   
The aggregated metric that you want to get the sum for, for example `sum({Revenue})`.   
For the engines MySQL, MariaDB, and Amazon Aurora with MySQL compatibility, the lookup index is limited to just 1. Window functions aren't supported for MySQL versions below 8 and MariaDB versions earlier than 10.2.

*sort attribute*   
One or more aggregated fields, either measures or dimensions or both, that you want to sort the data by, separated by commas. You can either specify ascending (**ASC**) or descending (**DESC**) sort order.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it's more than one word. The entire list is enclosed in [ ] (square brackets).

*start index*   
The start index is a positive integer, indicating *n* rows above the current row. The start index counts the available data points above the current row, rather than counting actual time periods. If your data is sparse (missing months or years, for example), adjust the indexes accordingly. 

*end index*   
The end index is a positive integer, indicating *n* rows below the current row. The end index counts the available data points below the current row, rather than counting actual time periods. If your data is sparse (missing months or years, for example), adjust the indexes accordingly. 

 *partition field*   
(Optional) One or more dimensions that you want to partition by, separated by commas.   
Each field in the list is enclosed in \$1\$1 (curly braces), if it's more than one word. The entire list is enclosed in [ ] (square brackets).

## Example


The following example calculates the moving sum of `sum(Revenue)`, sorted by `SaleDate`. The calculation includes two rows above and one row ahead of the current row.

```
windowSum
	(
	     sum(Revenue), 
	     [SaleDate ASC],
	     2,
            1
	)
```

The following example show a trailing 12-month sum. 

```
windowSum(sum(Revenue),[SaleDate ASC],12,0)
```

The following screenshot shows the results of this trailing 12-month sum example. The `sum(Revenue)` field is added to the chart to show the difference between the revenue and the trailing 12-month sum of revenue.

![\[alt text not found\]](http://docs.amazonaws.cn/en_us/quick/latest/userguide/images/windowSum.png)