linear regression

[carlos13]

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,

[vhespinog]

Hi Carlos

see the attached

[carlos13]

Thank you great idea,

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

[vhespinog]

may be like this, using a pivot table

[Anonymous]

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.

[carlos13]

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

[vhespinog]

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

Good luck

[vhespinog]

[sunny_talwar]

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

[sunny_talwar]

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;