Add calculated fields in a Pivot Table using LEFT() function

Question

I have a basic pivot table in Excel in which I want to add a calculated column, which describes year, and which is simply enough just the four leftmost characters from the Sale Date column.

Hence I do [Select somewhere in pivot table]->Analyze->Fields, objects,..

and then I use the settings:

Name: Year Formula: =LEFT('Sale DATE',4)

but this gives the error that Sale Date cannot be found. But, Sale Date is added from the available columns in the report, and thus is not misspelled.

Does anyone have any idea?

I've tried to use ; instead of , in the formula as I'm using a European version of Excel but then I got the same error as well.

EDIT

I've tried to do some basic calculations with the same approach (for instance creating a new field which is the Product of a field and a constant) and it has worked fine. Perhaps I'm using the wrong formula language, or that Pivot tables does not permit "advanced" formulae?

@pnuts I just want to find the Year from a Sale Date (of appaerence yyyymmdd), so for me a simply LEFT(Sale Date,4) or Year('Sale Date') would suffice. From all tutorials I've seen, Pivot Table never has any kind of function in its calculations. Is that not supported? I want to find the year so I later can create a Pivot Chart using all months on the x-axis and have lines corresponding to total sales per month and specific year. — Cenderze

jeffreyweir jeffreyweir · Accepted Answer · 2017-08-01T11:44:05

As pnuts says in his comment, Calculated Fields are not the way to go here. Instead, use the 'Group Selection' option to group by Years and Months:

...and then your PivotCharts will let you display the result like so:

Add calculated fields in a Pivot Table using LEFT() function

1 Answers