topic Re: Aggregated Measures with two aggregations at different levels. in SAS Visual Analytics

Aggregated Measures with two aggregations at different levels.

MarcelHaas — Fri, 19 Jun 2015 12:06:44 GMT

We have a problem. A simple example is the following:

We sell products, with an ID, that we also ship. In the table the following columns occur: ID, kind_of_costs, Number of products, Amount, eg:

A Product 12 2.00

A Transmission 12 .50

B Product 20 100.00

B Transmission 20 10.00

Herein, the total amount for ID A is 2.50 and 110 for ID B. The numbers of products would not have to be added together: of A, 12 items have been sold and shipped, and of B 20. If we now count the total number of products shipped, we must therefore count the number per ID only once (or use the average number per ID, it does not matter). Those numbers are always the same within one ID.

How can we now define a variable in VA (aggregated measure?) that we can use as the "number per ID", which we then still want to be able to use in an aggregated measure (note: aggregated Measures can not be used in a subsequent aggregated measure, and we want to do aggregations in various _ByGroup_'s). Any ideas? Thanks in advance!

PS. We use VA7.1.

Re: Aggregated Measures with two aggregations at different levels.

Meredith_SAS — Tue, 30 Jun 2015 18:42:45 GMT

Hi MarcelHaas,

In your source data, are there two rows of twelve for number per ID or are there different values that need to be accounted for. Because you have a column containing two, pre-aggregated values of twelve, we can take the average of those values, so that ID is twelve. However, if the values are different, they will not be the same as ID.

Thank you,

Meredith Bailey

Re: Aggregated Measures with two aggregations at different levels.

Lorrie_SAS — Mon, 06 Jul 2015 20:26:57 GMT

Hi MarcelHaas,

Have you tried creating a data query using the data builder (data preparation)? If not, here is some information that may be useful to you:

SAS(R) Visual Analytics 7.1: User's Guide

Best regards,

Lorrie

Re: Aggregated Measures with two aggregations at different levels.

Explore_SAS — Tue, 07 Jul 2015 15:10:49 GMT

have you tried using parameters in VA.

http://blogs.sas.com/content/sgf/2015/01/29/using-parameters-in-sas-visual-analytics/

Re: Aggregated Measures with two aggregations at different levels.

MarcelHaas — Thu, 09 Jul 2015 07:57:27 GMT

Hi Meredith,

The numbers would indeed all be the same. Nevertheless, the average can now in VA (I think) only be taken over a group that is used in the Visualisation. If I would want to show the average of the 12 and the 20 per year, it is not going to work if the number of rows containing the twelve and the twenty are different...

Cheers, Marcel

Re: Aggregated Measures with two aggregations at different levels.

MarcelHaas — Thu, 16 Jul 2015 07:12:57 GMT

It is not clear to me how to solve this issue with the data builder?

Re: Aggregated Measures with two aggregations at different levels.

MarcelHaas — Thu, 16 Jul 2015 07:13:44 GMT

I do not see how I can use a parameter to solve this. Do you have an example of how to use parameters here?

Re: Aggregated Measures with two aggregations at different levels.

Explore_SAS — Thu, 16 Jul 2015 13:21:15 GMT

one more option not sure that will work...

Right click on your measure and Create "Duplicate Data Item" and Then go to the properties section below it and change the Aggregation level to "Average".

Please let us know if that works..