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

1 ACCEPTED SOLUTION

Accepted Solutions

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

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

Discussion stats