Solved
Contributor
Posts: 72

# Create Aggregate Measures with filter criteria

How can we create multiple aggregate measures from the same table based on different filter criteria such as follows in the same Report Designer in SAS VA:

select distinct count(id) from abc where type in ('A','C') and month=month(today())-1

select sum(amount) from abc where type in ('A','C') and country='US'

select sum(c_bal) from abc where type in ('C') and region = 'NE'

I'm able to create aggregate measure but not finding filter criteria.  When I'm filtering it's applying on the whole job in the designer.  I just need to limit filter criteria for one aggregate measure only as I need to create multiple aggregate measure with different filter criteria.

Accepted Solutions
Solution
‎12-26-2017 07:08 AM
PROC Star
Posts: 392

## Re: Create Aggregate Measures with filter criteria

I think you could use the IF...THEN....ELSE:

First one:

Create a new calculated meassure:

IF type in ('A', 'C') and month = month(today())-1 then id else . You need to adjust the syntax in VA

Then create a distinct count on this variable

Second:

IF type in ('A', 'C') and country = 'US' then 1 else 0

Add sum as aggregation on the meassure.

Third:

IF type in ('C') and region = 'NE' then 1 else 0

Add sum as aggregation on the meassure.

//Fredrik

All Replies
Solution
‎12-26-2017 07:08 AM
PROC Star
Posts: 392

## Re: Create Aggregate Measures with filter criteria

I think you could use the IF...THEN....ELSE:

First one:

Create a new calculated meassure:

IF type in ('A', 'C') and month = month(today())-1 then id else . You need to adjust the syntax in VA

Then create a distinct count on this variable

Second:

IF type in ('A', 'C') and country = 'US' then 1 else 0

Add sum as aggregation on the meassure.

Third:

IF type in ('C') and region = 'NE' then 1 else 0

Add sum as aggregation on the meassure.

//Fredrik

☑ This topic is solved.