5 Replies Latest reply: Feb 16, 2010 9:30 AM by stephanHH RSS

    Problem with linest_m function

    stephanHH

      Hi!

      I've been following this forum quite a while and it has already helped me with a lot of issues, however I am now facing a problem for which I haven't found a solution yet!

      I've got the following fields:

      CompanyID

      Number of orders / day

      Now I want to display a trend in a table showing the gradient of the trend function over a period of time (7 days, 1 month, all-time)

      Looking back at my stats expierences from school and university this should surely be the gradient of the regression formula. There is a statistics formula for the gradient which is quite hard to calculate in Qlikview, but looking at the Help files and the handbook, Qlikview offers the Linest_m function which throws out the gradient.

      All good up to this point. I can display the gradient for a specific customer and a specific month using the following formula:

       

      linest_m( {<CUSTOMERID = {"ABC"}, MONTH = {"Jan*"}>} total aggr((sum( {<CUSTOMERID = {"ABC"}, MONTH = {"Jan*"}>} ORDERS)),DAY),DAY)


      Now I want to display the gradient in a table with the dimesion CUSTOMERID.

      Using:

       

      linest_m(total <CUSTOMERID> aggr ((sum(ORDERS)), DAY),DAY)


      or

       

      linest_m(total <CUSTOMERID> aggr ((sum(total <CUSTOMERID> ORDERS)), DAY),DAY)


      But this doesn't work out. Qlikview displays seemingly random figures in the first two rows and a "-" for all the other rows (Customers).

      Only if I select a specific Customer from a listbox I get shown the (correct) gradient in the table, but I would like to see all the values even if no Customer is selected so that one can see at a glance how the trend of incoming orders has been for a period of time. (Displayed as number and probably with a coloured arrow),

      I hope that you could understand what my problem is, and that somebody will be able to help as I have been experimenting for quite a while and couldn't get it to work.

      Maybe the linest_m approach isen't even the correct way to tackle the problem..any other suggetions are welcome!

      Thanks very much in advance!!

        • Problem with linest_m function
          MManders

          How about adding an expression like Sum() or Avg() around the linest_m() expression?

          The Avg() expression should then calculate the average slope of the current selected customers (or all if none is selected).

            • Problem with linest_m function
              stephanHH

              Hi Mark,

              sounded like a very good idea, but it didn't work. the AVG() expression gives back the same result as the linest_m() - but fails to give back the trend for each customer in one column.

              What I would like to get is something like this:

              CUSTOMER ORDERS/per month TREND/ 30 days TREND / 7 days

              ABC 1000 30 10

              XYZ 2000 60 90

              sum 3000 50 63,33

              What I see using linest_m( total aggr (sum( ORDERS), DAY), DAY) is:

              CUSTOMER ORDERS/per month TREND/ 30 days TREND / 7 days

              ABC 1000 50 63,33

              XYZ 2000 50 63,33

              sum 3000 50 63,33

              If I use a sum() expression around the linest_m expression I get wrong (really high) values.

              Maybe the linest_m() is the wrong approach for this kind of problem and there is a much easier solution!?

              Also I have got the "total" inside the linest_m() function under suspicion that it is responsible for disregarding the charts dimesion (Customer).

              But leaving total out of the expression doesn't work either.

              I hope that somebody might have an other idea to solve this problem!

              Thanks!

               

              Stephan