BookmarkSubscribeRSS Feed
KarunaTiwari
Obsidian | Level 7

Hi all,

 

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!!

Karuna Tiwari

17 REPLIES 17
MikeJenista
SAS Employee

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)?

KarunaTiwari
Obsidian | Level 7
Hi Mike,

We are calculating AHT for each employee and then bucketing them(employees) based on the AHT.
MikeJenista
SAS Employee

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'

KarunaTiwari
Obsidian | Level 7

Hi @MikeJenista,

 

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)))

 

Thanks!

MikeJenista
SAS Employee

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.

KarunaTiwari
Obsidian | Level 7
Hi Mike,

Thanks for your prompt response.
We tried exactly what you have suggested.

But the problem is if we create a calculated item, it will not allow aggregated items like AHT or Threshold Q1/Q3.
And aggregated item will not allow us to return 'Q1', 'Q2'.
MikeJenista
SAS Employee

What version are you using?  I can try setting up a similar scenario to see what I can figure out.  

MikeJenista
SAS Employee

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.

KarunaTiwari
Obsidian | Level 7

@MikeJenista : Can this be achieved through Stored Process?

JuanS_OCS
Amethyst | Level 16

Hello @KarunaTiwari,

 

yes it can be possible, my company is working with this approach.

 

However, you would need some javascript function to refresh contents in the report. 

KarunaTiwari
Obsidian | Level 7

Hi @JuanS_OCS

 

Please could you elaborate on the solution or guide us as to go about it.

 

Thanks!!

JuanS_OCS
Amethyst | Level 16

Hello @KarunaTiwari,

 

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.

    3.2 Worst case scenatio, you can always make a call to refresh the report/page, although it does not look good/professional to a user. https://stackoverflow.com/questions/3715047/how-to-reload-a-page-using-javascript // https://www.w3schools.com/jsref/met_loc_reload.asp

 

Clearer? 

 

KarunaTiwari
Obsidian | Level 7

@JuanS_OCS : Thanks for your inputs. We will try to implement and get back to you in case of any issues.

Thanks again!!

larryhems
Calcite | Level 5

Noticed that location.reload() is slower than the other methods.

 

location.reload(forceGet)

 

forceGet:

  • false - Default. Reloads the current page from the cache.
  • true - The current page must be reloaded from the server.

From the Mozilla Developement Network I guess the .reload method may fetch all files from the Server again. This would be similar to a CTRL + F5 reload.

 

The location.href for example, simply follows the link which may be cached. As for the MDN definition the behave is not clearly defined so I guess its browser and case specific behave.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 17 replies
  • 1873 views
  • 4 likes
  • 5 in conversation