using linest_R2 with two measures

[Anonymous]

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.

[Anonymous]

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.

[mohan_1105]

Hello Jay,

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

Regards,

Mohan

[Anonymous]

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.

[Anonymous]

Hi Jay,

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

[Anonymous]

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