Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Carlaf_93
Creator
Creator

forecasting

I've found this formula:

suppose:

you have as an expression:

Sum(Revenue)

 

to forecast it:

if(SUM(Revenue),SUM(Revenue), linest_m(total aggr(if(SUM(Revenue),SUM(Revenue)),Display_Date),Display_Date)*

only(Display_Date) +linest_b(total aggr(if(SUM(Revenue),SUM(Revenue)),Display_Date),Display_Date))

 


as a dimension :Display_Date

 

 

and add this to ur script:

Trend_Dates:

LOAD

Display_Date,

    Month(Display_Date) AS ForMonth,

    Year(Display_Date) AS ForYear;

LOAD

MonthStart(ADDMONTHS(TODAY(),RecNo()-1)) AS Display_Date

AUTOGENERATE 36;

 

 

Random_Finance:

LOAD

Month(Month_Start) AS ForMonth,

    Year(Month_Start) AS ForYear,

    Revenue;

LOAD

MonthStart(ADDMONTHS(TODAY(),RecNo()-1)) AS Month_Start,

    RAND()*100000 AS Revenue

AUTOGENERATE 12;

 

i want something like this. But i have a table like;

year_a

month_a

revenue_a

 

How can i forecast this? Do i need to use revenue_a instead of Revenue and month_a instead of Display_Date? 

it won't give me many years to see as forecasting.

Any suggestions?

1 Solution

Accepted Solutions
ArnadoSandoval
Specialist II
Specialist II

Hi @Carlaf_93 

I created a test application with the script information you supplied, it is attached to my reply; then you wrote about year_a, month_a and revenue_a without more details, so I assumed these columns corresponds to the columns in your Random_Finance table in the script. If my assumption is correct, you need to convert year_a and month_a to Display_Date (look at the sample's application's script, line 16); now, in your sample script, both tables feature the columns: ForMonth and ForYear, I renamed these column on the Random_Finance table to avoid a synthetic key.

Once that is done, I got this results (see image), visually, it seems the forecast line is working!

Test_Forecasting_01.png

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

5 Replies
ArnadoSandoval
Specialist II
Specialist II

Hi @Carlaf_93 

I created a test application with the script information you supplied, it is attached to my reply; then you wrote about year_a, month_a and revenue_a without more details, so I assumed these columns corresponds to the columns in your Random_Finance table in the script. If my assumption is correct, you need to convert year_a and month_a to Display_Date (look at the sample's application's script, line 16); now, in your sample script, both tables feature the columns: ForMonth and ForYear, I renamed these column on the Random_Finance table to avoid a synthetic key.

Once that is done, I got this results (see image), visually, it seems the forecast line is working!

Test_Forecasting_01.png

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

Carlaf_93
Creator
Creator
Author

Thank you! 

You explained it very well 🙂

I have another question... With this example we have got a linear forecasting...

i've seen some other forecasting where you have more lines predicted and then you can also see if you are in a specific range of values...

For those kind of analysis do you need python? Or is there  another way?

ArnadoSandoval
Specialist II
Specialist II

Hi @Carlaf_93 

Would you be able for you to illustrate your explanation with an screenshot? It is easier for me if I can see the visual effect of what you explained with words.

By the way, I found this topic in the community, it may help you: Calculating trend lines, values and formulas on charts and tables in Qlik Sense 

hth

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.
Carlaf_93
Creator
Creator
Author

Thank you.

I resolved my problem with a sort of line with the standard error effect...

The same line with a specific plus and less effect...

Carlaf_93
Creator
Creator
Author

.