Creating datasets on the fly - SAS VA

[KarunaTiwari]

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

[MikeJenista]

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]

Hi Mike,

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

[MikeJenista]

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]

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]

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]

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]

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

[MikeJenista]

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]

@MikeJenista : Can this be achieved through Stored Process?

[JuanS_OCS]

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]

Hi @JuanS_OCS, 

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

Thanks!!

[JuanS_OCS]

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]

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

Thanks again!!

[larryhems]

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.