Hello, I have this simple dataset as shown below MonthIncoming CallsAvg Incoming Call TimeOutgoing callsAvg. Outgoing Call TimeJan5103015Feb612405 I am trying to get it into this format MonthCall TypeNo of CallsAvg Call TimeJanIncoming510Jan Outgoing3015FebIncoming612FebOutgoing405 I thought it was going to be a simple calculation but I am not able to get it into the desirable format. I am using Transpose but I guess I am not using it the right way. Thanks for your help.

Cross Tab and Transpose

Hello,

I have this simple dataset as shown below

Month	Incoming Calls	Avg Incoming Call Time	Outgoing calls	Avg. Outgoing Call Time
Jan	5	10	30	15
Feb	6	12	40	5

I am trying to get it into this format

Month	Call Type	No of Calls	Avg Call Time
Jan	Incoming	5	10
Jan	Outgoing	30	15
Feb	Incoming	6	12
Feb	Outgoing	40	5

I thought it was going to be a simple calculation but I am not able to get it into the desirable format. I am using Transpose but I guess I am not using it the right way. Thanks for your help.

Transformation

Accepted answers

MarqueeCrew

@sagarb,

Here's a solution. Please review the posted solution. If I get time, I'll edit this post explaining why I solved it this way. A picture is worth a 1000 words:

Cheers,

Mark

Cross Tab and Transpose.yxmd

All comments

MarqueeCrew

@sagarb,

Here's a solution. Please review the posted solution. If I get time, I'll edit this post explaining why I solved it this way. A picture is worth a 1000 words:

Cheers,

Mark

Cross Tab and Transpose.yxmd

sagarb

Thank you for your reply. It's amazing that you did it in 15 mins when I was trying to do the same thing for an hr. Thanks again.

MarqueeCrew

@sagarb,

I'm humble about the time, so hopefully my head doesn't explode. I rushed the solution without explanation because I was doing this between phone calls. I saw the post and waited to see if someone would jump in. When they didn't, then I responded.

I recognized that you were actually transposing two times and re-assembling the call (regardless of type) data into rows. I pictured the process that I would perform manually and then translated it into Alteryx. I've been a user for practically 15 years.

Thanks for using the community. One day I hope to see you answering posts too. Remember, you don't have to be first with a solution. Your unique way of solving a problem might be something that inspires others to re-think their approach. Often friends in the community will post solutions and I'll have an aha moment.

Cheers,

Mark

anthony

How many words is this picture worth?

Hey Alteryx, this tool needs some UX love ... it solved a problem for me awhile back, by shear luck and perseverance. Even getting this output was a challenge as I had to keep deleting 'columns' because you cannot edit once created.

Here is the xml if someone wants to see how setup - create above workflow and drop this xml into D'arrange tool.

<Configuration>
<KeyFields orderChanged="False">
<Field name="Month" />
</KeyFields>
<OutputFields>
<Columns>
<Column name="Call Type" type="text" context="Take from Field Names" />
<Column name="No of calls" type="combobox" />
<Column name="average call time" type="combobox" />
</Columns>
<Data>
<r>
<c>Incoming Calls</c>
<c>Incoming Calls</c>
<c>Avg Incoming Call Time</c>
</r>
<r>
<c>Outgoing calls</c>
<c>Outgoing calls</c>
<c>Avg. Outgoing Call Time</c>
</r>
</Data>
</OutputFields>
</Configuration>

alex

Another way - less manual than arrange and without having to use the select tool.

Cross Tab and Transpose-AC.yxmd

tkhan

I try to use a formula after using Crosstab,

but it looks like the formula cannot see the new columns without the workflow running once.

So if I have 3 new columns that are generated as a result of the cross tab.

i can create the formula, and it works

I close the workflow, open it again, and it cannot see those columns,

please advise.

Thank you

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167