topic SV:Re: Problem with linest_m function in QlikView

Problem with linest_m function

Tue, 19 Jan 2010 09:43:30 GMT

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)

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

Wed, 20 Jan 2010 19:24:08 GMT

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

Fri, 22 Jan 2010 17:58:21 GMT

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

SV:Re: Problem with linest_m function

frank_carlsson — Wed, 27 Jan 2010 21:51:28 GMT

Have you tried adding "CUSTOMER" or "CUSTOMERID" in aggr()?

linest_m(aggr (sum( ORDERS), DAY,CUSTOMER), DAY)

SV:Re: Problem with linest_m function

Mon, 15 Feb 2010 09:08:05 GMT

I think Frank has the right idea ... add CUSTOMER as a parameter for the AGGR() function.

If that doesn't work, could you post your current application so we can take a look at it?

SV:Re: Problem with linest_m function

Tue, 16 Feb 2010 14:30:24 GMT

Hi!

While building a test-application for the community I tried it once more and it finally worked out!

So Problem solved, the addition of the CUSTOMERID to the aggr function worked!

Thanks very much for your help!

Stephan