2 Replies Latest reply: Mar 5, 2018 11:21 AM by Charles Demarest RSS

    Linest_B on a data subset

    Charles 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!!!

        • Re: Linest_B on a data subset
          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

            • Re: Linest_B on a data subset
              Charles 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.