1 of 1 people found this helpful
To get Year use the following calculation to get the first 4 characters
LEFT([Pivot Field Names],4)
To get the rest of it e.g. Units
MID([Pivot Field Names],5)
This will start with the 5th character and get everything after that
I've created a dummy data as per your format.
After pivot you'll getting this
You're very close to the expected output. You just need to split your Pivot field names field to get Years and Measures.
Use below easy calculations.
INT(LEFT([Pivot field names],4)) // to get year values (2015, 2016, 2017) convert it to INT so that you can use it in MAKEDATE() to convert it in a date //field. INT() is optional.
RIGHT([Pivot field names],LEN([Pivot field names])-4) //to get (ASP, Units etc.)
Convert Year to Date like this
Now drag your fields like this.
Hope this help. Workbook attached for you reference.
Pivot 9.3.twbx 7.8 KB
Cool! Try to close the thread by marking any of our answers so that it may help others who've similar query.