Create Aggregate Measures with filter criteria

[SAS_1001]

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.

Any insight would be helpful and thanks in advance !

[FredrikE · Accepted solution]

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