Accepted Solutions
If you're using SAS 9.4 and Visual Analytics 7.5, there is not going to be a great solution here. It will not do a double aggregation like you're looking for (average first, then sum the averages). You would need to precreate this in another table so that the table has a data item for the average already, and then VA will treat it like any standard measure and sum it or whatever you wanted.
If you have Viya 3.5 or Viya 4, there are two ways to achieve this.
The first is to create an aggregated datasource that uses your calculated item for average. The new datasource that gets created in your report will effectively be a standard measure, then you can add it to some table and the totals will sum it by default. More details on aggregated datasource can be found here (link to VA 8.5 for example):
https://go.documentation.sas.com/doc/en/vacdc/8.5/vareportdata/n1o2pvak2xw7jln13a2mxkytdmm4.htm
The second method is to create a new calculated item using the AggregateTable function. This function allows you to essentially create an aggregated table behind the scenes for that calculation and use it within your report objects. For example using CARS data, let's say I want to get Average MPG for certain car types. If I create a normal aggregated measure like you've done:
Then I end up in the same spot as you, my total is actually an overall average and not the sum of the averages:
But using the AggregateTable function, I can do this:
And this is really saying I want a table using Type and MPG (Highway) where MPG is averaged, then I want to Sum that when used in tables and graphs. In that same example, it looks like this:
You can see the averages per type match, so that number is right, but totals are different. The AggregateTable calculated item will sum it up. This approach will require you to include a specific category in the calculation like Type in my example, but should be able to get you closer to what you're looking for as long as it includes all of those categories I see in your crosstab.
Hi @Genesis007,
I am not sure fi I 100% understood, how your column is defined. But in the Data pane (here a screen shot out of Visual Analytics 7.5 you can select the column and set the "Aggregatiion:" to "Sum":
Best,
Markus
It's a tricky one. As VA chooses the kind of total aggregation of the aggregated column automatically, the only chance is to "outwit" VA. In VA 7.5 I managed to do it in an example:
The "Calc" column looks as follows:
So:
column "Calc" contains aggregated Averages, as I choose an if-clause which is always true.
But in the total aggregation of the column the sum of all data is used and not the average.
It looks as VA chooses the kind of aggregation for the total aggregation, which is used last in the formula for the aggregated column.
As this is based only on observations and no SAS-documentation, I would use it only under conditions, where I can check the results before forwarding them.
If you're using SAS 9.4 and Visual Analytics 7.5, there is not going to be a great solution here. It will not do a double aggregation like you're looking for (average first, then sum the averages). You would need to precreate this in another table so that the table has a data item for the average already, and then VA will treat it like any standard measure and sum it or whatever you wanted.
If you have Viya 3.5 or Viya 4, there are two ways to achieve this.
The first is to create an aggregated datasource that uses your calculated item for average. The new datasource that gets created in your report will effectively be a standard measure, then you can add it to some table and the totals will sum it by default. More details on aggregated datasource can be found here (link to VA 8.5 for example):
https://go.documentation.sas.com/doc/en/vacdc/8.5/vareportdata/n1o2pvak2xw7jln13a2mxkytdmm4.htm
The second method is to create a new calculated item using the AggregateTable function. This function allows you to essentially create an aggregated table behind the scenes for that calculation and use it within your report objects. For example using CARS data, let's say I want to get Average MPG for certain car types. If I create a normal aggregated measure like you've done:
Then I end up in the same spot as you, my total is actually an overall average and not the sum of the averages:
But using the AggregateTable function, I can do this:
And this is really saying I want a table using Type and MPG (Highway) where MPG is averaged, then I want to Sum that when used in tables and graphs. In that same example, it looks like this:
You can see the averages per type match, so that number is right, but totals are different. The AggregateTable calculated item will sum it up. This approach will require you to include a specific category in the calculation like Type in my example, but should be able to get you closer to what you're looking for as long as it includes all of those categories I see in your crosstab.
Hi @HunterT_SAS,
now that I have understood (thanks to your explanation in the other post) that also “the total” is subject to the formula for the aggregated column I think I found a way to get the result desired by @Genesis007. I adjusted the formula again:
For the IF-clause I choose all values of the “Department” column, so the only line not included is the total line, for which I can choose the aggregation method in the else clause.
This gives me the desired result:
Best,
Markus
(Averages for all departments, but the sum, not the average, in the total.)
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.
-
1675 views
-
7 likes
-
-
