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

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

Achraf_Ougdal — Wed, 07 Jun 2023 11:41:23 GMT

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

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

Madelyn_SAS — Wed, 07 Jun 2023 15:36:46 GMT

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

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

Achraf_Ougdal — Wed, 07 Jun 2023 15:46:06 GMT

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.

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

Madelyn_SAS — Wed, 07 Jun 2023 16:02:09 GMT

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

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

Achraf_Ougdal — Wed, 07 Jun 2023 16:30:20 GMT

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

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

acordes — Thu, 08 Jun 2023 17:17:20 GMT

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.

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

acordes — Thu, 08 Jun 2023 17:41:23 GMT

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

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

Achraf_Ougdal — Fri, 09 Jun 2023 10:35:45 GMT

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