Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Aggregated Measures with two aggregations at different levels.

Reply
Occasional Contributor
Posts: 13

Aggregated Measures with two aggregations at different levels.

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.

SAS Employee
Posts: 9

Re: Aggregated Measures with two aggregations at different levels.

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

Occasional Contributor
Posts: 13

Re: Aggregated Measures with two aggregations at different levels.

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

SAS Employee
Posts: 35

Re: Aggregated Measures with two aggregations at different levels.

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

Occasional Contributor
Posts: 13

Re: Aggregated Measures with two aggregations at different levels.

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

Contributor
Posts: 69

Re: Aggregated Measures with two aggregations at different levels.

Occasional Contributor
Posts: 13

Re: Aggregated Measures with two aggregations at different levels.

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

Contributor
Posts: 69

Re: Aggregated Measures with two aggregations at different levels.

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..

Ask a Question
Discussion stats
  • 7 replies
  • 632 views
  • 0 likes
  • 4 in conversation