topic Re: Creating datasets on the fly - SAS VA in SAS Visual Analytics

Creating datasets on the fly - SAS VA

KarunaTiwari — Mon, 09 Jul 2018 16:36:46 GMT

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

Re: Creating datasets on the fly - SAS VA

MikeJenista — Mon, 09 Jul 2018 18:00:27 GMT

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

Re: Creating datasets on the fly - SAS VA

KarunaTiwari — Mon, 09 Jul 2018 18:16:57 GMT

Hi Mike,

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

Re: Creating datasets on the fly - SAS VA

MikeJenista — Mon, 09 Jul 2018 18:29:23 GMT

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'

Re: Creating datasets on the fly - SAS VA

KarunaTiwari — Mon, 09 Jul 2018 18:35:50 GMT

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

Re: Creating datasets on the fly - SAS VA

MikeJenista — Mon, 09 Jul 2018 18:37:04 GMT

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

Re: Creating datasets on the fly - SAS VA

KarunaTiwari — Mon, 09 Jul 2018 18:38:29 GMT

We are using SAS VA 7.4.

Re: Creating datasets on the fly - SAS VA

MikeJenista — Mon, 09 Jul 2018 19:57:16 GMT

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.

Re: Creating datasets on the fly - SAS VA

KarunaTiwari — Tue, 10 Jul 2018 02:08:46 GMT

Thanks for your help, Mike!!

Re: Creating datasets on the fly - SAS VA

KarunaTiwari — Tue, 10 Jul 2018 07:28:10 GMT

@MikeJenista : Can this be achieved through Stored Process?

Re: Creating datasets on the fly - SAS VA

JuanS_OCS — Tue, 10 Jul 2018 07:42:54 GMT

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.

Re: Creating datasets on the fly - SAS VA

KarunaTiwari — Tue, 10 Jul 2018 09:40:01 GMT

Hi @JuanS_OCS,

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

Thanks!!

Re: Creating datasets on the fly - SAS VA

JuanS_OCS — Tue, 10 Jul 2018 09:49:01 GMT

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?

Re: Creating datasets on the fly - SAS VA

KarunaTiwari — Tue, 10 Jul 2018 10:11:57 GMT

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

Thanks again!!

Re: Creating datasets on the fly - SAS VA

KarunaTiwari — Mon, 16 Jul 2018 10:09:16 GMT

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!

Re: Creating datasets on the fly - SAS VA

MikeJenista — Mon, 16 Jul 2018 13:17:33 GMT

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.

Re: Creating datasets on the fly - SAS VA

larryhems — Mon, 07 Sep 2020 05:49:12 GMT

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.

Re: Creating datasets on the fly - SAS VA

cristydavidd — Tue, 20 Sep 2022 05:30:25 GMT

The reload() function takes an optional parameter that can be set to true to force a reload from the server rather than the cache. Since window.location.reload(true) has been deprecated, changing location.reload(true) to location.reload() to resolve this issue. Also, you can use:

window.location.href = window.location.href;

There are multiple ways to Refresh/Reload a page with jQuery/JavaScript, some are:

location.replace(location.pathname)
window.location = window.location
window.self.window.self.window.window.location = window.location