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; JanuaryFebruaryMarchTotalNon - SG EntityNon-SG FO 9,054 7,365 9,393 25,812Non - SG EntitySG FO 186,075 184,178 263,160 633,413SG EntitySG FO 412 217 295 924 Is there something in the Reporting tool i can use? Thanks!

@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 EntityFO LocationMonthFebruaryJanuaryJuneMarchNon - SG EntityNon-SG FOFebruary7365 Non - SG EntityNon-SG FOJanuary 9054 Non - SG EntityNon-SG FOMarch 9393Non - SG EntitySG FOFebruary184178 Non - SG EntitySG FOJanuary 186076 Non - SG EntitySG FOJune 1 Non - SG EntitySG FOMarch 263160SG EntitySG FOFebruary217 SG EntitySG FOJanuary 412 SG EntitySG FOMarch 295 It needs to look like the table below; RecordIDTrade EntiryFO LocationJanuaryFebruaryMarchTotal1Non - SG EntityNon-SG FO9,0547,3659,39325,8122Non - SG EntitySG FO186,075184,178263,160633,4133SG EntitySG FO412217295924 I am having trouble re arranging the Cross Tab table to attach prefix's. Any advise would be appreciated

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!

Publish

Datasets

Tips and Tricks

Data Investigation

Accepted answers

All comments

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.

0310-ZahinOsman.yxmd

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.

0310-ZahinOsman-r1.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703