Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

using linest_R2 with two measures

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.

5 Replies
Highlighted
Creator II
Creator II

Re: using linest_R2 with two measures

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.

Highlighted
Partner
Partner

Re: using linest_R2 with two measures

Hello Jay,

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

Regards,

Mohan

Highlighted
Contributor
Contributor

Re: using linest_R2 with two measures

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.

Highlighted
Contributor III
Contributor III

Re: using linest_R2 with two measures

Hi Jay,

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

Highlighted
Contributor
Contributor

Re: using linest_R2 with two measures

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