07-09-2018 12:36 PM
I have a data set like :
LOB Employee Date(Monthyear format) Talktime Handlecalls
503 123456 Jan2018 400 20
503 123457 Feb2018 423 12
I have 2 Global Filters on the Top - by LoB and Date(MonthYear)
1 is dropdown for LoB
2nd is Slider with To and From Date(MonthYear)
I create an aggregated measure AHT = Talktime/Handlecalls in SAS VA.
Now I want to create quartile buckets(0-25%,25-50%,50-75%,75-100%) for the aggregated measure based on the LoB and Date range selections.
Please can anybody guide me as to how to go about it.
Thanks in advance!!
07-09-2018 02:00 PM
Can you be more precise about what you want the quartile buckets to do? Are you trying to calculate the AHT for each employee, and then bucket their values by quartile?
Or are you trying to calculate the AHT in four quartiles of employees (bucketed in some other way)?
07-09-2018 02:29 PM
Okay, I think there is a possible way to do this. You can create three aggregated items, 'Threshold Q1', 'Threshold Q2', 'Threshold Q3'. These would just aggregate AHT, but instead of Sum or Average you would define each one to use a different quartile cutoff (Q1, median, Q3 I think are the correct options).
Then you can create a new calculated item something like this:
if AHT < Threshold Q1, then return 'Q1'
else if AHT < Threshold Q2, then return 'Q2'
else if AHT < Threshold Q3, then return 'Q3'
else return 'Q4'
07-16-2018 06:09 AM
This is how Qlikview handles quartiles at the visualization layer (very straightforward). Will SAS have something similar in future versions?
=if(aggr(sum(HandledTime)/sum(Calls), Agent) <= fractile(TOTAL aggr(sum(HandledTime)/sum(Calls), Agent), 0.25), 1,
if(aggr(sum(HandledTime)/sum(Calls), Agent) <= fractile(TOTAL aggr(sum(HandledTime)/sum(Calls), Agent), 0.50), 2,
if(aggr(sum(HandledTime)/sum(Calls), Agent) <= fractile(TOTAL aggr(sum(HandledTime)/sum(Calls), Agent), 0.75), 3,4)))
07-16-2018 09:17 AM
The latest version of VA on Viya does have more flexible aggregation features. With the use of an aggregated temp table, users can write expressions similar to the Qlikview code you presented.
07-09-2018 02:35 PM
07-09-2018 03:57 PM
Okay, I see that the problem is that the quartile/median aggregations need to have the aggregated AHT values available for ranking, so there is no way to figure out the quartile cutoffs from row-level data alone.
You can create an order-able object like a list table, and rank all the employees by AHT. But I don't think it is possible to automatically extract the cutoffs from this either. If the number of calls and talk time were already condensed for each employee, this would work since AHT would be a row-level calculation and not an aggregation. But with the need to aggregate over the time period, I don't think you can dynamically obtain the bucketing you want.
07-10-2018 03:42 AM
yes it can be possible, my company is working with this approach.
07-10-2018 05:49 AM
I cannot not give a lot of details just by myself, since I am not doing functional work, but I can share with you as much as I know:
1. You can stored processes, that can be triggered in SAS VA with a button, a link or an action.
2. The consider a stored process as a normal code, and a normal code can access, if it has enough permission, to any SAS dataset/library. Therefore, after the trigger, you can update a table or create a new one, as you would do with common code. Do you know how to access LASR tables with code?
3. If you need this table for a VA report, and I guess you do, you will need to refresh the report, or the object part of the report that shows that new data.
3.1 The good option is the second one, to refresh only the section of the report (not all the report) but I just don't know if it is even possible, or how. Perhaps @MikeJenista or some other SAS colleague can answer this part. I would be VERY interested.