Hi,
I am working on SAS® Visual Analytics Release: 8.5.2 - SAS® Viya® release: V.03.05.
I have a data set formed by different tables. One, with values for my variable with 4 dimensions (Period, Location, Activity, Metric), and the rest with hierarchical levels for the 4 dimensions of my variable. Not all the dimensions have the same number of hierarchical levels, but they can be as high as 11.
For the purpose of explaining what I am trying to do, I have created a simplified dummy dataset and reduced my tables to just 3, 2 for two hierarchical levels – see attached file for reference:
-one table with values by metric, location and period (I have reduced one dimension here).
Period | Location | Metric | Value |
Apr-20 | continent | number of people | 100 |
Apr-20 | country | number of people | 50 |
Apr-20 | city | number of people | 20 |
Apr-20 | continent | Cost | 10000 |
Apr-20 | country | Cost | 5000 |
Apr-20 | city | Cost | 2000 |
-one table with hierarchical levels for period
| | |
Period 1 | Period 2 | Period 3 |
EOY | Quarter1 | Apr-20 |
EOY | Quarter1 | May-20 |
EOY | Quarter1 | Jun-20 |
-one table with hierarchical levels for location
Location 1 | Location 2 | Location 3 |
continent1 | country1 | city1 |
continent1 | country2 | city2 |
continent1 | country2 | city3 |
continent2 | country4 | city4 |
I have joined all tables and created 2 hierarchies (for location and period). I want to present the data in a crosstable, where I have my location hierarchy on the left-hand side and period hierarchy at the top, so I can expand data across the hierarchical levels.
| Period Hierarchy |
Location hierarchy | value |
Where value is aggregated for location and period, from bottom to top in both hierarchies if the metric is cost, but if metric is number of people I want the value to be aggregated across the location hierarchy, so the value for continent1 will be the sum of all the child location levels, but averaged across the period hierarchy, I want to get for continen1 the quarterly and annual average of the sum of all the child location levels.
I have created a variable to do this, and it’s working well for cost but I cannot seem to be able to get the average for the Quarter/year of the aggregated values across the location hierarchy. I am getting the overall average of all data at the most granular level but not aggregated by location.
Can anyone advise on how to do this?
Thank you,
Laura
