Pivot table - Format Cross Tab

[ZahinOsman]

Hi guys,

I have the following pivot tables (Cross tab tool) below, and I need to do some format amendments;

As highlighted, I am trying to switch January and February around. 

For the 2nd screenshot, I am trying to format the data/table into something like below;

		January	February	March	Total
Non - SG Entity	Non-SG FO	9,054	7,365	9,393	25,812
Non - SG Entity	SG FO	186,075	184,178	263,160	633,413
SG Entity	SG FO	412	217	295	924

Is there something in the Reporting tool i can use?

Thanks!

[Qiu]

@ZahinOsman 
The Cross Tab tool will arrange the columns based on the Accending Order of the Column Names, so we need some Prefix to tract the order of the columns.
I usually do something as below.

[nicsintila]

You can use the Table Tool to format the table if you want to use the reporting tools. Simply re-arrange the columns on the per column configuration tab.

You may also do this using the select tool if the column arrangement is the only issue.

 

[aatalai]

@ZahinOsman you could also use the select tool after the crosstab, admit that can be a bit manual though

[ZahinOsman]

Thanks all! let me try the suggestions

[ZahinOsman]

How do I arrange it from Cross Tab results?

ultimately you are correct in your final output, this is what I want to achieve

[ZahinOsman]

@Qiu Hey I tried your method but realized the input you have is already in the desired format. Could you elaborate?

So in the below screenshot I have the following Cross Tab 

Table

Trade Entity	FO Location	Month	February	January	June	March
Non - SG Entity	Non-SG FO	February	7365
Non - SG Entity	Non-SG FO	January		9054
Non - SG Entity	Non-SG FO	March				9393
Non - SG Entity	SG FO	February	184178
Non - SG Entity	SG FO	January		186076
Non - SG Entity	SG FO	June			1
Non - SG Entity	SG FO	March				263160
SG Entity	SG FO	February	217
SG Entity	SG FO	January		412
SG Entity	SG FO	March				295

It needs to look like the table below;

RecordID	Trade Entiry	FO Location	January	February	March	Total
1	Non - SG Entity	Non-SG FO	9,054	7,365	9,393	25,812
2	Non - SG Entity	SG FO	186,075	184,178	263,160	633,413
3	SG Entity	SG FO	412	217	295	924

I am having trouble re arranging the Cross Tab table to attach prefix's. Any advise would be appreciated

[Qiu]

@ZahinOsman 
You are right.

So we can convert the String of Month to Month Numbers with DateTime function then we can have the correct order.