Aggregation of an aggregation

[CameronF]

Pretty sure this isn't possible, but for the benefit of my superiors: is there any way of getting VA to perform an aggregation on an aggregated measure?

E.g. Sum _ByGroup_ (x - Avg_ByGroup (x) )

Met with an error any time I try to enter an existing aggregated measure into a new aggregated measure formula, VA says it's expecting a measure.

Version 7.3, using Data Builder to pre-aggregate the data not an option.

[SASKiwi]

I haven't tried this myself but in theory you could calculate one aggregation outside of VA using SAS code, load the data including the aggregation, then do the second aggregation in VA. 

[CameronF]

Thanks for the reply SASKiwi. Unfortunately that's not an option in our particular case since the dataset is quite large (or rather, would be made enormous by calculating aggregations according to every possible combination of the filters we wish to use in the report). We would simply perform both aggregations using SAS code otherwise.

[SASKiwi]

What about doing an aggregation at the very lowest level only outside VA? Then VA itself can do the higher level ones.

A good analogy would be using PROC SUMMARY with the NWAY option (I'm hoping you are familiar with this) to get the most granular summary stats. All higher levels can be derived from the NWAY level.

[CameronF]

As above - if that were an acceptable solution then we would simply generate all the required statistics outside of VA.

We want the calculations to be dynamic based on the user's selections (filters, displayed variables, displayed measures). If calculating every possible combination of these variables outside VA is the only way to get VA to display the correct statistics, then there seems very little reason for us to use VA at all.

[SASKiwi]

I suggest you check out this interesting post by my mate @ChrisNZ. He explains the approach much better than me:

https://communities.sas.com/t5/SAS-Visual-Analytics/Making-VA-reports-more-dynamic-category-measure-...

[CameronF]

I think he hits the nail on the head when he says:

To obtain this flexibility, the data has to be pre-summarised for all crossings (nway), and then summarised again for each subpopulation split. This means that the size of the data set can increase considerably compared to the original detail data.This procedure defeats the main purpose of VA’s, which is to load detail data in memory, and to derive aggregations on the fly as the user navigates the report.

Besides which, his approach might work okay when displaying counts, but as soon as you start using percentages (e.g. percent of column subtotal) the Sum _ByGroup_ function starts behaving really weirdly when applying filters.

I suspect we're asking more of VA than it's been designed to do, but even so, it's something that we'd be able to work around if it were able to perform aggregations on aggregated measures. Which - as your mate suggests - shouldn't be too difficult for a product that's supposed to let you do this sort of thing. I guess I'm just curious to know why VA can't do it given that it's a relatively simple extension of what it can already do:

Calculation A - Fine

Calculation B - Fine

Calculation A, then factor that in to Calculation B - Not fine

[itchyeyeballs]

As a new VA user I have been reading this thread with interest, I've been a bit supprised by VA's lack of functionality in this area, forcing the user to pre-process the data seems to go against the advertised purpose of allowing non technical users to analyse data.

"Self-service, ad hoc visual data discovery and exploration put lightning-fast insights within everyone's reach."

Tableau has LOD & table calculations that give it this kind of functionality and even bog standard MS Excel can leverage DAX through power pivot to aggregate 100 million + rows to any level on the fly.

The one advantage I can see of the SAS approach is that the final reports will be less complex for users (I guess it also stops VA canibalising the market for other SAS products)

[SASKiwi]

Thanks for the detailed explanation @CameronF. This is an area of interest to me too as a very new SAS VA user who is not yet pushing the boundaries of what VA has to offer and who has not explored all the possibilities. VA is as yet immature in some areas of functionality but is on a quicker development path than other SAS products. I'm expecting some significant improvements in the next release. For example more complete use of HTML5, maybe being able to export from the modern view and so on....

BTW I see the ability to pre-process and load VA data all in one step as a significant benefit in itself, regardless of whether you use it to work around current VA limitations or not.

I'm hoping that other more experienced VA users can add to this discussion.

[itchyeyeballs]

 @CameronF 

My SAS contact has given me a possible way of achieving this, I have only had a chance to test very briefly so there may be a gottcha I've not hit yet but it seems to work at a simple level at least.

1. Instead of creating an aggregated measure as the first step, duplicate your base measure and set the default aggregation to what you need it to be.
2. Create a new aggregated measure and use the duplicate measure created above 

In my test I used a very simple data set to test an average of an average that also allows me to filter and group dynamically