Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Create Aggregate Measures with filter criteria

Accepted Solution Solved
Reply
Contributor
Posts: 72
Accepted Solution

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.

 

Any insight would be helpful and thanks in advance !


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 Smiley Happy

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

View solution in original post


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 Smiley Happy

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 348 views
  • 2 likes
  • 2 in conversation