topic using linest_R2 with two measures in QlikView

using linest_R2 with two measures

Anonymous — Mon, 31 Jul 2017 13:54:45 GMT

I have two measures that I want to calculate a slope line and an R-squared for. The formulas for the two measures are:

X: Sum(VisitCounter)/Sum( {<Category = {'Provider'}>} Hours)

Y: Sum( {<Category = {'Business Support'},Category = {'Clinical Support'}>} Hours)/Sum( {<Category = {'Provider'}>} Hours)

I cannot figure out how to get this done. Thanks.

Re: using linest_R2 with two measures

Anonymous — Wed, 02 Aug 2017 08:41:53 GMT

Hi Jay,

Can you give an example of how you would want the end result to look like?

Possibly with some sample data.

Regards,

Bas.

Re: using linest_R2 with two measures

mohan_1105 — Tue, 08 Aug 2017 12:45:55 GMT

Hello Jay,

create a variable X and Y, and use linest_R2( $(Y) , $(X) ) in the KPI.

Regards,

Mohan

Re: using linest_R2 with two measures

Anonymous — Fri, 15 Sep 2017 19:55:44 GMT

Thank you.

I tried it but it didn't work. My X and Y variables (see formulas above) are both measures.

X=Provider Worked Hours per Visit

Y= Ratio of Clinical and Business Associate Worked Hours to Provider Worked Hours

The dimension would be the combination of the location code and the date, or just the date.

I spent about 2 hours playing with this but I haven't figured it out yet. It seems like LINEST_SR needs one dimension and only one measure. Any advice you could provide would help.

Re: using linest_R2 with two measures

Anonymous — Thu, 14 Dec 2017 12:30:18 GMT

Hi Jay,

Were you able to find solution ?
I too tried similar approach, but it is not working.

Re: using linest_R2 with two measures

Anonymous — Fri, 29 Dec 2017 13:27:38 GMT

I did get a solution, it had to do with how I loaded my data into Qlik. Here is the pertinent code;

Test1:
Load
   W_PUCC & '_' & Text(Date([Date],'YYYY-MM-DD')) AS LocDay,
   Sum(SupportHours)/ Sum(ProviderHours) as SupportToProviderRatio,
   Sum(ProviderHours) as TotalProviderHoursPerDay
Resident API_WorkedHoursData
Group by Date, W_PUCC ;

Test2:
Load
LocDay,
sum(VisitCounter) as VisitCountPerDay
Resident KioskData
group by LocDay;

Left Join (Test1)
Load *
Resident Test2;

Drop table Test2;

Test:
Load *,
VisitCountPerDay/TotalProviderHoursPerDay as VisitsPerProviderHour
Resident Test1;

Drop table Test1;

Then I put linest into a KPI:

= linest_R2 (VisitsPerProviderHour, SupportToProviderRatio)

(Credit to Marian Rubin for coming up with the code for Test, above)

VisitsPerProviderHour is the dependent variable, SupportToProviderRatio is the independent variable