Create Aggregate Measures with filter criteria

SAS_1001 — Wed, 20 Dec 2017 15:20:35 GMT

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 !

Re: Create Aggregate Measures with filter criteria

FredrikE — Fri, 22 Dec 2017 08:04:52 GMT

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

topic Create Aggregate Measures with filter criteria in SAS Visual Analytics

Create Aggregate Measures with filter criteria

Re: Create Aggregate Measures with filter criteria