Ignore filter functionality for (non-periodic) aggregate measure calculations

Rabbott89 — Mon, 17 Oct 2022 01:13:32 GMT

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 Division	Agent	Calls Answered	Division Avg	% of Division Avg
Division A	Agent 1	10	11	90.91%
Division A	Agent 2	8	11	72.73%
Division A	Agent 3	11	11	100.00%
Division A	Agent 4	15	11	136.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 Division	Agent	Calls Answered	Division Avg	% of Division Avg
Division A	Agent 2	8	11	72.73%

rather they would simply see:

Agent Division	Agent	Calls Answered	Division Avg	% of Division Avg
Division A	Agent 2	8	8	100.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: https://communities.sas.com/t5/SAS-Visual-Analytics/How-to-exclude-an-object-from-being-filtered-by-a-report-prompt/td-p/619601 ) 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.

Re: Ignore filter functionality for (non-periodic) aggregate measure calculations

acordes — Mon, 17 Oct 2022 20:54:08 GMT

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.
Done

Re: Ignore filter functionality for (non-periodic) aggregate measure calculations

Rabbott89 — Thu, 27 Oct 2022 00:39:16 GMT

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!

topic Re: Ignore filter functionality for (non-periodic) aggregate measure calculations in SAS Visual Analytics

Ignore filter functionality for (non-periodic) aggregate measure calculations

Re: Ignore filter functionality for (non-periodic) aggregate measure calculations

Re: Ignore filter functionality for (non-periodic) aggregate measure calculations