0 Replies Latest reply: Dec 20, 2016 11:48 AM by John Simmons RSS

    Creating my own TrendLine

    John Simmons

      I'm trying to create my own trend line (because for some inexplicable reason, QlikView's built in trend lines look like a line you'd see on the old 4-bit Atari consoles).

       

      Here's what I'm doing:

       

      I have a chart object with a bar series showing the data (labeled as "RVU").

      Sum({<MonthSerial={">=$(=MAX(MonthSerial)-24)<=$(=Max(MonthSerial))"}>}Prov_Agg_Total_RVU)

       

      Next, I have a series of hidden expressions:

       

      Labeled as "Aggr_RVU"

      AGGR(RVU, MonthSerial)

       

      Labeled as "Slope"

      linest_m(Aggr_RVU, [MonthSerial])

       

      Labeled as "Intercept"

      linest_b(Aggr_RVU, [MonthSerial])

       

      And finally, the visible expression to show the trend line (labeled as "Trend")

      (Slope * rowno()) + Intercept

       

      The bars for the "RVU" display, but I don't get a "Trend" line, and I don't get a legend at all. What am I doing wrong?

       

       

      EDIT ==========================

       

      I  also tried the following:

       

       

      ONLY({1}$(=(LINEST_M(AGGR(Sum({<MonthSerial={">=$(=MAX(MonthSerial)-24)<=$(=Max(MonthSerial))"}>}Prov_Agg_Total_RVU),MonthSerial),MonthSerial) * rowno())) +
      $(=LINEST_B(AGGR(Sum({<MonthSerial={">=$(=MAX(MonthSerial)-24)<=$(=Max(MonthSerial))"}>}Prov_Agg_Total_RVU),MonthSerial), MonthSerial)))

       

       

      Which resulted in a flat line (and the legend is now being displayed). The flat line is of course, wrong.