forecasting

[Carlaf_93]

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?

[ArnadoSandoval · Accepted solution]

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!

Hope this helps,

[Carlaf_93]

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]

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

[Carlaf_93]

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]

.