Last() returns the value of
an expression evaluated with a pivot table's dimension values as they appear in the
last column of the current row segment in the pivot table. This function
returns NULL in all chart types except pivot tables.
Information noteSorting on y-values in charts or sorting by expression columns in tables is not allowed when this chart function is used in any of the chart's expressions. These sort alternatives are therefore automatically disabled. When you use this chart function in a visualization or table, the sorting of the visualization will revert back to the sorted input to this function.
Syntax:
Last([TOTAL] expr
[, offset [, count]])
Arguments
Argument
Description
expr
The expression or field containing the data to be measured.
offset
Specifying an offsetn, greater
than 1 moves the evaluation of the expression n columns to the left from the last column.
Specifying an offset of 0 will evaluate the expression on the last column of the current row segment.
Specifying a negative offset
number makes the Last function work like the First
function with the corresponding positive offset
number.
count
By specifying a third parameter count greater than 1, the function will return a range of values, one for each of the
table columns up to the value of count, counting to the left from the original cell.
TOTAL
If the table is one-dimensional or if the qualifier TOTAL is used as argument, the current column segment is always equal to the
entire column.
If the pivot table has multiple horizontal dimensions, the current row segment will include only columns with the same values as the current
column in all dimension rows except for the row showing the last horizontal
dimension of the inter-field sort order. The inter-field sort order for horizontal dimensions in pivot tables
is defined simply by the order of the dimensions from top to bottom.
Example: Chart expressions
Example
Result
Last( Sum(Sales ))
Returns the sum of Sales from the last column of the current row segment.
Last( Sum(Sales ), 2)
Returns the sum of Sales from the column that is two columns to the left of the last column in the current row segment.
Last( Total Sum( Sales ))
Returns the total sum of Sales from the last column of the current row segment.
RangeAvg (Last(Sum(x),1,5))
Returns an average of the results of the sum(x)
function evaluated on the five rightmost columns of the current row segment.
Example - Last fundamentals
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a data table called Example.
Load the data and open a sheet. Create a new pivot table and add this field as a row dimension:
Year
Add this field as a column dimension:
Quarter
Create the following measures:
=Sum(Sales), to calculate the sum of sales.
=Last(Sum(Sales)), to calculate the sum of sales of the last column for the current row segment.
Results table
Year
Q1
Q2
Q3
Q4
Sum(Sales)
Last(Sum(Sales))
Sum(Sales)
Last(Sum(Sales))
Sum(Sales)
Last(Sum(Sales))
Sum(Sales)
Last(Sum(Sales))
2023
5000
7000
4000
7000
6000
7000
7000
7000
2024
4500
7250
3250
7250
6500
7250
7250
7250
Looking at the results, you can see that the Last function returns the sum of sales value for the last column of each row segment for each quarter. For example, in Q1 the Sum(Sales) value for that period is 5000. The Last(Sum(Sales) value for Q1 is 7000, which is the value for the Sum(Sales) in Q4, the last column value of the current row, 2023.
Example - Last scenario
Overview
A dataset contains sales figures for various products over a three-month period. The company wants to present the total sales for the current period and the sales growth compared to the last (or most recent) sales figures in the period.
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a data table called Example.
Load the data and open a sheet. Create a new pivot table and add this field as a row dimension:
Product
Add this field as a column dimension:
Date
Create the following measures:
=Sum(Sales), to calculate the annual sales.
=(Last(Sum(Sales)) - Sum(Sales)) / Sum(Sales), to calculate the growth in sales from the current period to the latest period. To show this value as a percentage, under Number formatting, select Number > Formatting Simple > 12.34%.
Results table
Product
2024-01-01
2024-02-01
2024-03-01
Sum(Sales)
(Last(Sum(Sales)) - Sum(Sales)) / Sum(Sales)
Sum(Sales)
(Last(Sum(Sales)) - Sum(Sales)) / Sum(Sales)
Sum(Sales)
(Last(Sum(Sales)) - Sum(Sales)) / Sum(Sales)
Apple
100
50.00%
110
36.36%
150
0.00%
Banana
150
100.00%
200
50.00%
300
0.00%
Carrot
120
33.33%
80
100.00%
160
0.00%
In this example, you can see how sales have progressed from the start of the sales period to the latest period. For example, in the first period 2024-01-01, Apple sales were 100 compared to the latest sales figures of 150 for the period 2024-03-01. This indicates an increase of 50%.
Example - Using Last to calculate budget variance
Overview
A dataset contains budget figures by quarter over a two-year period. The company wants to present the total budget amount for each quarter and the variance between the budget amount for the current period compared to the average quarterly budget for the year.
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a data table called Example.
Load the data and open a sheet. Create a new pivot table and add this field as a row dimension:
Year
Add this field as a column dimension:
Quarter
Create the following measures:
=Sum(Budget), to calculate the budget for the quarter.
=Sum(Budget) - RangeAvg(Last(Budget, 1, 4))to calculate the variance between the Budget amount for the current period compared to the average quarterly budget for the year.
Results table
Year
Q1
Q2
Q3
Q4
Sum(Budget)
Sum(Budget) - RangeAvg(Last(Budget, 1, 4))
Sum(Budget)
Sum(Budget) - RangeAvg(Last(Budget, 1, 4))
Sum(Budget)
Sum(Budget) - RangeAvg(Last(Budget, 1, 4))
Sum(Budget)
Sum(Budget) - RangeAvg(Last(Budget, 1, 4))
2023
1200
-50
1100
-150
1300
50
1400
150
2024
1550
105
1230
-215
1400
-45
1600
155
Using the Last function, you can see how the budget amount per quarter varies compared to the average quarterly budget. For example, in 2023, the total budget for the year is 5000 (1200 + 1100 + 1300 + 1400), therefore, the average quarterly budget is 1250. Since the budget amount for Q1 is 1200, it is 50 less compared to the average quarterly budget of 1250, whereas the budget variance for Q4 is 150 over the average quarterly budget.
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!
When you visit any website, it may store or retrieve information on your browser, mostly in the form of cookies. The information does not usually directly identify you, but it makes the site work as you expect it to and can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.
Privacy & Cookie Notice
Manage Consent Preferences
Strictly Necessary Cookies
Always Active
These cookies are necessary for the website to function and cannot be switched off in our systems. They are usually only set in response to actions made by you which amount to a request for services, such as setting your privacy preferences, logging in or filling in forms. You can set your browser to block or alert you about these cookies, but some parts of the site will not then work.
Functional Cookies
These cookies enable the website to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies, then some or all of these services may not function properly. These cookies do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device.
Performance Cookies
These cookies allow us to count visits and traffic sources so we can measure and improve the performance of our site and make it easier to navigate. For example, they help us to know which pages are the most and least popular and see how visitors move around the site. When analyzing this data it is typically done on an aggregated (anonymous) basis.
Advertising Cookies
These cookies may be set through our site by our advertising partners to build a profile of your interests and show you relevant advertisements on other sites. They do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device. If you do not allow these cookies, you will experience less relevant advertising.