After importing your file to tableau use custom split to split columns with "[" as delimiter. Then first field will be what you want.
Connect to your excel and Pivot the data by selecting all the year headers
after Pivoting click on pivot field and Split and use the Separator "["
You will get the desired data set
after slight manipulation you will get the final view
Hope this helps kindly mark this answer as helpful or correct so that it will help others
As suggested by our community members,
You can use custom split to get the initial values.
However, find my approach below,
You need to first transpose your data so that all the year columns convert into rows. You can do that using Pivot option which inbuilt feature of tableau.
Once done then you can use custom split on the values. But only split will not help post split still some of the values containing "<" characters that you need to remove so apply REPLACE() function to get rid of such unwanted values. Try below formula
INT(TRIM(REPLACE(TRIM( SPLIT( [Values], "[", 1 ) ),'<','')))
Once done then you're good to go for your analysis with the data. Once sample viz I've already created for your reference. Let us know if this help.
Book1_v10.2.twbx 71.6 KB
Thank you very much. Got it.
Thank you very much. Yes it answered my question.