Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

YTD Crosstab report

Reply
New Contributor
Posts: 3

YTD Crosstab report

[ Edited ]

Hi,

 

This is my first post on this forum. Very new to SAS VA (started using last) and any help will be highly appreciated. 

 

I have a requirement to build a YTD report in a crosstab (it can be any other object type as well but I am trying to use crosstab). I have attached a document with Sample data in the first table and the YTD report in the second tab (sheet 1). I am expected to create a report exactly as shown in "sheet 1"

 

I am able to get the below correctly but as soon as I add the third column "Budget Room Revenue" as a measure it appears as a row but I need it as a column on the right of "Last year". So when I change it to a "category" it goes all messy and I am totally clueless how can I even add 'Budget Variance" and "Last year Variance" columns in the way required. Can anyone please help. I tried using hierarchy but it didn't work for me. May be I need to change the data structure to get it to work?

 

ItemsActualLast Year
  RR21,042,94218,130,146
  FB_R11,660,94610,308,000
  OOD Revenue2,296,4472,293,568
  Rental & other Income1,027,019977,613
Total Revenue36,027,35431,709,328

 

Apologies in advance if anything in this post is not according to the forum rules.

SAS Super FREQ
Posts: 322

Re: YTD Crosstab report

[ Edited ]

Hi arkam,

 

The sample table provided has separate columns for everything:

 

2016 Rental Revenue 2017 Rental Revenue 2016 Rental Revenue Budget 2017 Rental Revenue Budget 2016 Sports Club Revenue 2017 Sports Club Revenue 2016 Sports Club Revenue Budget 2017 Sports Club Revenue Budget
144 145 114 113 114 113 114 113

 

It might be better if the data could be restructured more like this:

 

Year Market Revenue Budget
2016 Rentals 145 114
2016 Sports Club 143 111
2017 Rentals 146 118
2017 Sports Club 142 112

 

Crosstabs in VA will always have all the measures on the same axis, and the column and row headings are driven by category values (or hierarchy levels, which are also categorical values). Your data basically does not have any categorical data, so some restructuring is needed to create a useful crosstab.

 

 

Does this help? I don't think that the exact crosstab in your XLS sheet is possible even with restructuring, but you should be able to create a useful crosstab to display your data.

 

 

Thanks

Sam

 

New Contributor
Posts: 3

Re: YTD Crosstab report

Thanks for the suggestion. As you mentioned, even after restructuring I am not able to get the report in the way mentioned in that tab. Is there any way of creating such a report? Doesn't have to be crosstab..

SAS Super FREQ
Posts: 322

Re: YTD Crosstab report

What are the key features of the visualization that you want to make?

 

In your spreadsheet you have a crosstab of sorts where the horizontal categories have different measures. You can't do this in VA, as far as I know. Each value on the horizontal axis will have all of the measures. I don't think any other report object would do what you want.

 

Can you share a screenshot of what you are able to create at this point? (You can blank out the cell values in an image editor.) This would make it easier for us to help you.

Ask a Question
Discussion stats
  • 3 replies
  • 135 views
  • 0 likes
  • 2 in conversation