ColumnNo - chart function

Overview

A dataset contains quarterly sales data. This example multiplies the sales value for each period by the column number of the period.

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • Period

  • Sales

Load script

Example:
Load * inline [
Period, Sales
Q1, 1000
Q2, 2000
Q3, 3000
Q4, 4000
];

Results

Load the data and open a sheet. Create a new pivot table and add this field as a column dimension:

• Period

Create the following measure:

• =Sum(Sales) * ColumnNo(), to calculate the sum of the sales values multiplied by the column number, which is the quarter in this example.

The sum of sales value for each quarter is multiplied by the output of the ColumnNo function. For example, the value of sales in Q4 is 4000, and the output of the ColumnNo function returns 4 because Q4 is the fourth column. Therefore, the measure expression multiplies 4000 by 4 and returns 16000.

Overview

A dataset contains quarterly sales figures. A company wants to increase the sales data for Q1 and Q3 (the odd-numbered columns) by 5% and increase the sales for the remaining columns (Q2 and Q4) by 10%.

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • Period

  • Sales

Load script

Example:
Load * inline [
Period, Sales
Q1, 1000
Q2, 2000
Q3, 3000
Q4, 4000
];

Results

Load the data and open a sheet. Create a new pivot table and add this field as a column dimension:

• Period

Create the following measures:

• =Sum(Sales), to calculate the sum of sales.

• =If(Odd(ColumnNo()), Amount * 1.05, Amount * 1.10), to increase the sales amounts for the odd-numbered columns by 5% and the remaining columns by 10%.

The results show that the sales values for the odd-numbered columns, 1 and 3 (Q1 and Q3), have increased by 5%, while the remaining even-numbered columns, 2 and 4 (Q2 and Q4), have increased by 10%.

Overview

This example uses the same dataset as the previous scenario to present the cumulative total for the values in each period.

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • Period

  • Sales

Load script

Example:
Load * inline [
Period, Sales
Q1, 1000
Q2, 2000
Q3, 3000
Q4, 4000
];

Results

Load the data and open a sheet. Create a new pivot table and add this field as a column dimension:

• Period

Create the following measure:

• =RangeSum(Before(Sum(Sales), 0, ColumnNo())), to calculate the cumulative total.

This example shows how to use the ColumnNo function to generate a running total. The following explanation provides more details about the measure expression: RangeSum(Before(Sum(Sales), 0, ColumnNo())).

Sum(Sales): calculates the sum of sales for the current cell in the pivot table.

Before(Sum(Sales), 0, ColumnNo()): The Before function retrieves values from the preceding columns in the pivot table.

• The Sum(Sales) argument ensures that the function retrieves the summed Sales for the preceding cells.

• The offset value of 0 specifies the offset for the column (remaining in the current row segment of the current column).

• ColumnNo() determines how many columns to go back when retrieving the data. It dynamically represents the position of the current column (for example, for column 3, ColumnNo() equals 3, so the function looks back across three columns).

RangeSum(): The RangeSum function calculates the sum of the range of values provided. It handles null values by treating them as zero. In this expression, RangeSum effectively generates a cumulative total by summing all the preceding Sales values retrieved by the Before() function, as well as the sales of the current cell.