aggregated measure based on different levels of hierarchy

[amr_waly]

Hi 

i am trying to create an aggregated measure (average based on date hierarchy level)  to be displayed on a line chart on SAS VA.  if i am on day level in hierarchy  i need to calculate the average per day (Total Sum/Number of days in the level), when i am on month level the calculation should be (Total Sum/Number of Months in the level), and so on. the data should resembled like the following:-

month    no. of months        profit       average per month (sum of profit /count of months)
jan                3                          6               9
feb                3                          9               9
mar               3                        12               9

so if i drill down through date hierarchy from Month level to day level the data should resembled like the following:-

month    day     no. of days     profit     average per day (sum of profit/no. of days)

jan           1             2                 10               15

jan           2             2                 20               15

Feb         1              3                 10                10

Feb         2              3                 15                10

Feb         3              3                 15                10

unfortunately the calculated measure can be created based on the category items not the hierarchy, any suggestions on how to overcome this point.

thanks

[jltz83]

We have had a similar request, and subsequently concluded that this type of functionality is not currently available in VA (we are on 7.4). If I understand correctly, you essentially need to store an aggregate measure to be referenced at a different level in a hierarchy. Aggregate measures cannot be used as parameters in this way. All behind-the-scenes data manipulations operate on the row level data. Aggregations are controlled by report object selections and filters, and they cannot be stored or processed dynamically outside the object and then referenced within the object. A stored process could accomplish this, but stored process objects and other report objects cannot share interactions. The solution we arrived at was storing additional rows in our dataset that include the aggregate measures, in your example this would be "no. of months" and "no. of days." Calculated items could then be created to display in the line chart, and possibly work with the date hierarchy.

It is a convoluted approach, but we haven't arrived at a better solution. I'll be curious to learn if you find something better.