Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
carlos13
Creator
Creator

linear regression

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,

9 Replies
vhespinog
Contributor III
Contributor III

Hi Carlos

see the attached

carlos13
Creator
Creator
Author

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
Contributor III
Contributor III

may be like this, using a pivot table

Anonymous
Not applicable

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
Creator
Creator
Author

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
Contributor III
Contributor III

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

Good luck

vhespinog
Contributor III
Contributor III

1.JPG2.JPG3.JPG4.JPG5.JPG

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

Capture.PNG

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;