concatenate and summarize?

tiverson

I am trying to figure out what is the best way to transform my data so that it comes out in the layout below:

If I want the CO and posting period displayed by columns, do I need to concatenate them?  Or is there a way for them to be displayed on their own line?

I also need to summarize the different categories (by color code). 

Data

Desired Output

sum data.xlsx

mceleavey

Hi @tiverson ,

what you need is the Crosstab tool. This will allow you to group records accordingly and create the concatenation function of your choice, in this case Sum:

I first created the  sum at group level by category, then by Orange/Yellow. These streams are then unioned together.

You will need to do the same with the percentage calculation as I don't have time, but it's simply recreating the logic but calculating the % row by CO/Period combination. 

Workflow attached,

I hope this helps.

M.

mceleavey solution.yxmd