04-11-2018 10:56 AM - edited 04-11-2018 11:02 AM
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?
|Rental & other Income||1,027,019||977,613|
Apologies in advance if anything in this post is not according to the forum rules.
04-12-2018 11:36 AM - edited 04-12-2018 11:38 AM
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|
It might be better if the data could be restructured more like this:
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.
04-14-2018 01:59 PM
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..
04-14-2018 03:38 PM
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.