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:
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.
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?
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: