Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- SAS Viya
- /
- Visual Analytics
- /
- Conditional aggregation of a single measurement in Visual Analytics(3....

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 09-30-2021 12:07 PM
(302 views)

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:

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

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

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.