Need help with a complex Cross-Table in SAS Visual Analytics 7.5

[Achraf_Ougdal]

Hello everyone,

I need help with a pretty complex crosstable as shown in the picture below : 

I want to create this table (I made it with excel). In lines I want to have the different products in my data source. In columns I want the year (Specifically only the last 2 years but that's not the important part).

In measures I have the value, Weight and Price. along with their variation from previous year (Value and percentage).

I know that the yellow part of the table is straightforward and It's easy. but the purple part is a nightmare. as the variation must change parameters for each line (Value (2023)-value(2022) in the first line and weight(2023)-weight(2022) in the second line etc...) AND it needs to be in columns (seperated from the other measures)

I tried everything I could to make it happen but I couldn't.

Can anyone please assist me ?

Thank you

[Madelyn_SAS]

Have you tried creating period calculations. See the topic "Create Derived Calculations." 

[Achraf_Ougdal]

I did calculate the variation for each measure. My issue is their position. Since the value, weight and the price and their variation are all measure I couldn't find a way to make some of them on lines while other in columns. I hope I was able to clarify my need. 

[Madelyn_SAS]

You cannot put some measures on columns and some on rows. 

[Achraf_Ougdal]

So I assume that there's no workaround to make that table with that exact layout  in SAS Visual Analytics

[acordes]

What makes it complex is your excel cross table affinity.

With Excel you can assemble data in a format that is not logical and feasible from a data table perspective.

You can create easily the calculations, but it's difficult or impossible to match your design. 

[acordes · Accepted solution]

With Precision container and 3 copies of the cross table you can come close. 

[Achraf_Ougdal]

That's the best way to do it so far to be honest, even if it will impact the performance but it's the only way.

Thank you so much