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
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.
