Obsidian | Level 7

## SAS drop down selection and aggregation

Hi everyone,

I have the input table:

 ID Low Level High Level Cost A 0 1 23 A 1 0 23 B 1 0 42 B 0 1 42 C 0 0 12 C 1 0 12 D 0 0 5

meaning that an ID can be in both Level.

What I would like to do is to have a drop down selection for Level.

If I select Low Level. Only low level average cost display (average cost = (23 + 42 + 12) / 3 =25.7, and there are 3 unique IDs

 Only Low Level(Low Level = 1 and High Level = 0) Average Cost Number of ID 25,7 3

If I select High Level. Only high level average cost display (average cost = (23 + 42) / 2 =32.5, and there are 2 unique IDs

 Only High Level(Low Level = 0  and High Level =1) Average Cost Number of ID 32,5 2

As default (no selection), it will take into account all level with unique ID. (average cost = (23 + 42 +12 + 5) / 2 =20.5, and all unique IDs are counted.

 All (Unique ID) Average Cost Number of ID 20,5 4

How can we do that for this drop down aggregation?

1 ACCEPTED SOLUTION

Accepted Solutions
SAS Super FREQ

## Re: SAS drop down selection and aggregation

So with that change and using a drop-down control we get this:

10 REPLIES 10
SAS Super FREQ

## Re: SAS drop down selection and aggregation

Hello,

Would it make sense to create a calculated column "Level" with an expression like this?

IF ( ( 'High Level'n = 1 ) AND ( 'Low Level'n = 0 ) )
RETURN 'High Level'
ELSE (
IF ( ( 'Low Level'n = 1) AND ( 'High Level'n = 0 ) )
RETURN 'Low Level'
ELSE 'Neither' )

Then you could assign this Level column to a button bar control.

I think that would achieve the result that you are looking for. An extra clause might be needed to handle a "Both" case where both values are 1. I don't know if that is possible in your data.

Let us know if that helps,

Sam

Obsidian | Level 7

## Re: SAS drop down selection and aggregation

Thanks for the answers. Ideally, an ID can't be either High or Low. So you mean now we have 4 drop down selection for Level Column ('HIgh', 'Low', 'Neither', ' ' ) (Empty blank for all).

But I wonder the rule for aggregation in VIYA will be a little bit different when when no selection (All is considered).

SAS Super FREQ

## Re: SAS drop down selection and aggregation

I think that the expression I gave above should work. You will have a button bar like this:

[[High Level] [Low Level] [Neither]]

If the user does not make a selection on the button bar, then all values of the column will be displayed. There is no need for an additional "All" value in the calculated column.

Sam
Obsidian | Level 7

## Re: SAS drop down selection and aggregation

I see. but when there is no selection, the table should only display unique values, as seen in the last table only 4 unique IDs are aggregated.
SAS Super FREQ

## Re: SAS drop down selection and aggregation

I am getting this output using your data:

I calculated the distinct count for ID. To eliminate "Neither" from the button bar, I just created a filter on the button bar to remove that value.

The Cost value when nothing is selected is the average for the full data set, rather than the average of the averages for the ID. I believe there is a workaround for this if you want the value to be aggregated that way, but I don't remember how to do it.

You could also use a drop-down list control instead of a button bar. When there are only two selections, I like button bars.

Is this closer to what you wanted?

Thanks,
Sam

SAS Super FREQ

## Re: SAS drop down selection and aggregation

Replacing Cost with this calculation will give you 20.5:

AggregateTable(_Avg_, Table(_Avg_, Fixed('ID'n), 'Cost'n))

SAS Super FREQ

## Re: SAS drop down selection and aggregation

So with that change and using a drop-down control we get this:

Obsidian | Level 7

## Re: SAS drop down selection and aggregation

That's great thank you very much for your help
Obsidian | Level 7

## Re: SAS drop down selection and aggregation

Thank you very much. It works well.
SAS Super FREQ

## Re: SAS drop down selection and aggregation

Excellent! Thanks for letting us know
Discussion stats
• 10 replies
• 902 views
• 11 likes
• 2 in conversation