4 Replies Latest reply on Nov 28, 2016 9:34 PM by Mahfooj Khan

# Need Help in Pivoting ???

Hi Tableau Lovers,

This is with respect to data preparation.

Scenario : I am working on Tableau workbook which is already created by some x Developer. Now Business wants to add news view to the existing tableau workbook.

Aim- To get the Trend View in Tableau. To create Line Chart (X - axis as Time )

Problem with Data Set- Years and Measures in Data set are spread horizontally.

As we know in ideal condition year column should be in single column, but in this case Year Column are spread horizontally.

My Approach :- To create Pivot and bring data into the required format.

But here is the challenge.

I have column with Year and Measure together.

For example-
2015ASP
2016ASP
2017ASP
2018ASP

and then

2015Units
2016Units
2017Units

and then

2015Tech Pen
2016Tech Pen
2017Tech Pen

Attached is the image for your reference. Last Table is Expected Result.

Please let me know how to get it done.

Rajeev Pandey Ashish Chaudhari Mahfooj Khan Kindly have a look on this .

• ###### 1. Re: Need Help in Pivoting ???

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

1 of 1 people found this helpful
• ###### 2. Re: Need Help in Pivoting ???

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.

Years:

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.

Measures:

RIGHT([Pivot field names],LEN([Pivot field names])-4) //to get (ASP, Units etc.)

Convert Year to Date like this

MAKEDATE([Years],1,1)

Now drag your fields like this.

Hope this help. Workbook attached for you reference.

Mahfooj

3 of 3 people found this helpful
• ###### 3. Re: Need Help in Pivoting ???

Bingoo !!! Its working

Thanks a lot Mahfooj Khan and Bora Beran for solution.

• ###### 4. Re: Need Help in Pivoting ???

Cool! Try to close the thread by marking any of our answers so that it may help others who've similar query.

Mahfooj