Linest_B on a data subset

[demarest]

Hi,

I have a dataset which consists of Yield, Return and Portfolio.  There are only two portfolios in the dataset, one is for a benchmark "H0A0" and the other call it "ABCD", a managed portfolio.  What I want to do is a linear regression of Yield on to Return just for the benchmark in order to compare it with the Yield/Return on our portfolio.  QlikSense was able to parse this and provide results in a KPI:

aggr(LINEST_B(simple_return,Yield_To_Worst_Pct),portfolio={'H0A0'})

If within my app, I click select just the Benchmark, I match with the Intercept function in Excel on just the benchmark (good), but if I select Benchmark and portfolio, it calculates the formula over the benchmark and ABCD. If I select just ABCD, the KPI blanks out.  I was hoping that the above sytax would filter on records where Portfolio = H0A0, but that is not what it seems to be doing.  Is there a better way to get LINEST_B / LINEST_M for a subset of the data only?

Thanks!!!

[sunny_talwar]

Is this expression giving any output?

aggr(LINEST_B(simple_return,Yield_To_Worst_Pct),portfolio={'H0A0'})

The above doesn't even seem to be correct expression... I wonder how it is working to give any output

[demarest]

Hi Sunny. Thanks for the reply. The parser still calls this an OK expression: aggr(LINEST_B(simple_return,Yield_To_Worst_Pct),parser dont {'mind'}). But it returns no results (a dash).  However, when using the expression from the above, aggr(LINEST_B(simple_return,Yield_To_Worst_Pct),portfolio={'H0A0'}), if in my app (by way of a bar chart), I select:

(1) Just the benchmark, I get the desired result - LINEST_B that matches Excel for just the benchmark.

(2) Both the benchmark and the portfolio selected, it is calculating LINEST_B on both the portfolio and benchmark.  I am hoping for just the benchmark

(3)  Just the portfolio, I get a dash

What I was hoping for is for LINEST_B on just the benchmark for case (2).  My question is really less about this particular syntax so much as how I can get the desired result: ie. LINEST_B on just the subset of the data where portfolio = 'H0A0', when both the data for portfolio = 'H0A0' and portfolio = 'ABCD' are selected.