You can add functions to reports with pivoted dimensions. These include lookup, pivot column, pivot index, pivot offset list, and pivot where.

Lookup

The lookup pivot function identifies specific content within a table and returns predefined results when a match is found. Lookup requires three arguments: what you're looking for, where to look for it (the field), and what to return if it is found.

For example, someone comes to your store and it was their birthday party last night. They're preparing thank-you cards, but they don't remember who gave them a gift that cost $17.57 from your store. They don't have a receipt, don't know who purchased the item, and aren't sure of which location the gift was purchased at. To find the customer:

1. Start a table using a dimension of customer, pivoting a dimension of multi-store location, and using a measure of sales total.

   

2. In the calculation editor, enter the lookup and the number for the cost of the purchase, in this case 17.57.

   

3. For the second argument, enter the sale line total: ${sale_lines.total_sales_no_tax}.

   

4. For the third argument, set it to display the customer's full name: ${customers.full_name}).

   

   Your calculation should look like: lookup(17.57,${sale_lines.total_sales_no_tax},${customers.full_name})

5. Save the calculation.

The calculation will find a match to a customer from a specific store.

You can also create a copy of the calculation to get the information of which row the customer is on instead of the exact customer.

Pivot column

Pivot columns require zero arguments and return the number of the column, ascending from left to right. The calculation is: pivot_column().

After saving, the number of the columns in the report will be ascending from left to right.

Pivot index

Pivot index is positioned at the far right of your table and returns the content from column X. Pivot index requires two arguments: which dimension or measure you want to view, and how many columns you want in your pivoted results.

For example, the calculation should look like: pivot_index(${sale_lines.total_sales_no_tax},1)

After you save the calculation, the index column displays.

Pivot offset

Returns the content from X columns to the right. Requires two arguments: which dimension or measure you want to view and how many columns to the right you want view. It will return null when the table runs out of columns. 
For example, the calculation is: pivot_offset(${sale_lines.total_sales_no_tax},1)

After saving the calculation and running it, the report will display the Pivot Offset columns.

Pivot offset list

Pivot offset list creates a list of values in the same row and requires three arguments: which measure you want to view, how many rows to the right you want to start the list, and how many rows to the right you want to include in the list.

For example, if your calculation is: pivot_offset_list(${sale_lines.total_sales_no_tax},0,3)

After saving the calculation and running it, the Pivot Offset List columns display in your report.

Pivot where

The Pivot where function returns the pivoted column's row total when a dimension's criteria are met. It requires two arguments: a logical test and the desired column.

For example, if your calculation is: pivot_where(${sales.time_stamp_quarter_of_year}="Q2",${sale_lines.total_sales_no_tax})

After saving the calculation and running it, the column displays in the report.