BookmarkSubscribeRSS Feed
MarcelHaas
Calcite | Level 5

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.

7 REPLIES 7
Meredith_SAS
Calcite | Level 5

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

MarcelHaas
Calcite | Level 5

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

Lorrie_SAS
SAS Employee

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

MarcelHaas
Calcite | Level 5

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

MarcelHaas
Calcite | Level 5

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

Explore_SAS
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 7 replies
  • 2008 views
  • 0 likes
  • 4 in conversation