BookmarkSubscribeRSS Feed
Winter
Fluorite | Level 6

Hi!

I'm working on a Visual Analytics report that utilize the Suppress function when calculating different measurements (If there is less than 5 observations then suppress). The end user of the report now wants to add an additional level on this logic since suppression is only required for a specific category of observations, simply put,

 

IF Type=B and number of observations < 5 then Suppress ELSE "calculate measurement". I've been prying at this for weeks but haven't manage to get it to work thus I wonder, is this logic possible?

 

Below is a simplified example

Let's establish some sample data:

Type Observation
A 1
A 2
A 3
B 1
B 2

 

Imagine that I want to create a conditional Sum calculation of Observation that is always allowed to be shown when Type="A" but can't be shown when Type="B" In this case the desired result would be:

Type Conditional Sum
A 6
B *

 

Suppression is an aggregated measurement so lets simplify the problem:
Is it possible in Visual Analytics to create a measurement that is the Sum of Observation when Type="A" and average of Observation when Type="B"?

I've tried to express this in Visual Analytics as such:

Winter_0-1633015917843.png

However, when I use this calculated measurement in a table I get the following result:

Winter_1-1633016116462.png

In fact, whenever I try to use IF/ELSE logic in combination with a aggregated calculations it always results in the ELSE condition.

 

Conclusion 
I understand that the IF logic expressed above evaluates each row in the data while the Sum and Avg is performed "by each group" and thus it's kind of doing two very different things.
I'm guessing what I'm looking for is something in the line of "IF current _ByGroup_ (Type) = "A" RETURN ..... ELSE ....." however I haven't found any operators that does that.

 

While the problem sounds simple, at this point I'm prepared to call it impossible due to how _ByGroups_ works but I'm hoping that I might be wrong 😄

 

Best regards and thanks in advance for any suggestions!

//Winter 

 

 

1 REPLY 1
acordes
Rhodochrosite | Level 12
Probably you can achieve this by creating an aggregated table from the original one.
So you carry both aggregated measures, sum and average and its groupby variable to the newly aggregated table.
There you can apply your if-then logic but instead of using the dynamic aggregated measures you take their static copy.
It's static in the context of the aggregated measure but the aggregated table updates as well and reacts to parameters and filters.
Give it a try

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 710 views
  • 0 likes
  • 2 in conversation