topic Re: linear regression in QlikView

linear regression

carlos13 — Thu, 01 Jun 2017 15:58:13 GMT

How can I calculate and apply a linear regression to see the balance of future dates?

From the data shown in the Excel I need to calculate with the methodology of linear regression of future dates, the real is from 02/28/2017 backwards and the future dates 01/03/2017 forward and the amounts are zero, I need to replace it with the projection, if you can help me I thank you.

Regards,

Re: linear regression

vhespinog — Thu, 01 Jun 2017 18:40:05 GMT

Hi Carlos

see the attached

Re: linear regression

carlos13 — Thu, 01 Jun 2017 19:40:26 GMT

Thank you great idea,

Is there any way to apply this formula but in the script per day? In the load, not per month

Re: linear regression

vhespinog — Thu, 01 Jun 2017 20:35:28 GMT

may be like this, using a pivot table

Re: linear regression

Anonymous — Fri, 02 Jun 2017 15:18:16 GMT

Any chance you can provide some screen shots or QVD? QlikView Personal refuses to open this document, and Sense only shows the load script. I'm interested in the command scripts / settings used.

Re: linear regression

carlos13 — Fri, 02 Jun 2017 16:30:09 GMT

What happens is that if I want to select a date of 2018, I do not calculate or maintain the forcasting, so I would like to know if it can be done in the load, so that I can see a specific day and keep the forcasting

Re: linear regression

vhespinog — Fri, 02 Jun 2017 17:28:32 GMT

not sure if it is possible because aggr is a chart function, if you find the solution, tell us

Good luck

Re: linear regression

vhespinog — Fri, 02 Jun 2017 17:34:21 GMT

Re: linear regression

sunny_talwar — Fri, 02 Jun 2017 17:54:28 GMT

May be use this as your expression

LINEST_M({<DATE>}TOTAL Aggr(If(Sum({<DATE>}MONT), Sum({<DATE>}MONT)), DATE),DATE)*DATE

+LINEST_B({<DATE>}TOTAL Aggr(If(Sum({<DATE>}MONT), Sum({<DATE>}MONT)),DATE),DATE)

Here I am ignoring selection in DATE field because Year filter is created using Date(Year) on the front end, but if you have Year field and you will make selections there, I would add or replace Year. Essentially, you would want to add all date and time related fields to your set analysis

Re: linear regression

sunny_talwar — Fri, 02 Jun 2017 18:11:37 GMT

Doing it in script might look like this

Table:

LOAD DATE,

Date(monthstart(DATE), 'MMM-YYYY') as MonthYear,

CUENTA,

NAME,

MONT

FROM

[DATA.xls]

(biff, embedded labels, table is Sheet1$);

Left Join (Table)

LOAD LINEST_M(Sum, DATE) as Slope,

LINEST_B(Sum, DATE) as Intercept;

LOAD DATE,

If(Sum(MONT), Sum(MONT)) as Sum

Resident Table

Group By DATE;

FinalTable:

LOAD *,

(Slope*DATE) + Intercept as Forecast

Resident Table;

DROP Table Table;

But is this something you need for each Name or is it across all the names? If it is different based on Name, then try this

Table:

LOAD DATE,

Date(monthstart(DATE), 'MMM-YYYY') as MonthYear,

CUENTA,

NAME,

MONT

FROM

[DATA.xls]

(biff, embedded labels, table is Sheet1$);

Left Join (Table)

LOAD NAME,

LINEST_M(Sum, DATE) as Slope,

LINEST_B(Sum, DATE) as Intercept

Group By NAME;

LOAD DATE,

NAME,

If(Sum(MONT), Sum(MONT)) as Sum

Resident Table

Group By DATE, NAME;

FinalTable:

LOAD *,

(Slope*DATE) + Intercept as Forecast

Resident Table;

DROP Table Table;