BookmarkSubscribeRSS Feed
Calcite | Level 5

My first post so please forgive/educate me on any posting bad practice. This is really a general functionality point but I'll explain it within the context of what I'm trying to do. 


I am looking to include measures in a SAS VA 8.4 report to show, for example, the number of phone calls answered by an agent as a percentage of the average number of phone calls answered by all agents in the same division.


This is easy enough to do. Using the AggTable operator I can create a measure that always shows the Division level average and then use that as the denominator in my percentage of calculation. For example, it's easy enough to create a cross tab object that looks like this (made up numbers):


Agent DivisionAgentCalls AnsweredDivision Avg% of Division Avg
Division AAgent 1101190.91%
Division AAgent 281172.73%
Division AAgent 31111100.00%
Division AAgent 41511136.36%


The issue I have is that I would like users of the report to be able to filter for/select specific agents but if I introduce a report/page/object level filter for Agent, that filter appears to apply before the (innermost) AggTable calculation is performed such that, if someone were to filter for say Agent 2 they would NOT see the following desired result:


Agent DivisionAgentCalls AnsweredDivision Avg% of Division Avg
Division AAgent 281172.73%


rather they would simply see: 

Agent DivisionAgentCalls AnsweredDivision Avg% of Division Avg
Division AAgent 288100.00%

because the filtering has already taken place prior to the Division Avg calculation. Of course I can put the Division Avg in a separate unfiltered object and get the right answer (which appears to be all they wanted to do in this closed thread: ) but what I really want to do is use it to get a "normalised" score within the same object.


Unlike for some of the periodic aggregate operators, there doesn't appear to be functionality within SAS VA 8.4 to ignore (certain) filters in AggTable calculations. Is this planned for a future release? Am I missing a trick here? 


Without it I can't see how you can display group level amounts in an object/page/report that's filtered to a member/sub-population of that group (which can't be that rare of a thing to want to do). 


I could perform the group level calculations in the data prep phase but then they are not dynamic (for example users couldn't alter the time period over which the calculations are performed via a date slider).


Any help would be much appreciated.


Rhodochrosite | Level 12
You can achieve this by doing the following.
Create a dummy variable character like "all".
Create an AGGREGATED DATA TABLE using the dummy variable, the incoming phone calls, the answered phone calls in the sum aggregation context.
Then left join the original table with the aggregated table on the dummy variable.
Calculate the percentage in the joined table.
Apply the filter to the joined table.
Calcite | Level 5

Thanks @acordes,


I think you're suggesting summing over the entire table and remerging the result with the original table.


That will work for the basic table I put in my post as an illustration. Really that table is already a cross tab summarising more detailed underlying data.


Nevertheless, in the absence of an ignore filter type functionality,  a slightly more complex "group-by-remerge" type approach (either within VA or in the data prep phase) is probably the way to do it so thank you!



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. 

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
  • 2 replies
  • 2 in conversation