Urgent Help Please | Forecasting data

[Qlik1_User1]

Hi All

Need one help please, to show the projections in Qlik. I have never done this before and new to this. searched many articles but not getting anything

I have a fact tables has data like below

Client Year   Amount
A       2023    100
A      2024      700
A      2025     600
B      2023    2000
B      2024    1000

Expected Output - to show the forecasted data for next 2 year means max(year)+2 per client.

Client Year   Amount
A       2023    100
A      2024      700
A      2025     600
A     2026      forecasted value
A     2027    forecasted value
B     2023     2000
B    2024      1000
B    2025      forecasted value
B    2026      forecasted value

[JandreKillianRIC]

Hi @Qlik1_User1 

How would the forecast be calculated? Building the years out per client would be the easy part but depending on how you would forecast this will affect the calculation. 

But just doing a SMA (Simple Moving Average) here is the code

// Step 1: Load the existing data
Data:
Load * Inline [
    Client, Year, Amount
    A, 2023, 100
    A, 2024, 700
    A, 2025, 600
    A, 2026, 800
    B, 2023, 2000
    B, 2024, 1000
    B, 2025, 1500
];

// Step 2: Find the last year for each client
MaxYear:
Load
    Client,
    Max(Year) as LastYear
Resident Data
Group By Client;

// Step 3: Generate the next two years for each client
Concatenate(Data)
Load
    Client,
    LastYear + 1 as Year,
    Null() as Amount, // You can replace Null() with a forecasted value if needed
    Client as Forecast
Resident MaxYear;

Concatenate(Data)
Load
    Client,
    LastYear + 2 as Year,
    Null() as Amount, // You can replace Null() with a forecasted value if needed
    Client as Forecast
Resident MaxYear;

Drop Table MaxYear; 

// Get the forecast - Just doing a Simple Moving Average (SMA)
Forecast: 
Left Join (Data)
Load 
    Sum(Amount)/Count(DISTINCT Year) as ForecastAmount,
    Client as Forecast
Resident Data
Where Not Isnull(Amount)
Group By Client; 

// Add the forecast amount if Amount is null 
FinalTable: 
Load 
	Client, 
    Year, 
    IF(Isnull(Amount), ForecastAmount, Amount) as FinalAmount
Resident Data; 

Drop Table Data; 

Regards Jandre

[diegozecchini]

Hi!
In Qlik, you can achieve this forecast using Aggr() and RangeAvg() functions or by pre-processing the data in Qlik Script.
You can modify your load script to generate forecasted values dynamically for the next two years.

FactTable:
LOAD Client,
Year,
Amount
FROM YourSource;

TempYears:
LOAD
Client,
Max(Year) AS MaxYear
Resident FactTable
Group By Client;

Concatenate (FactTable)
LOAD
Client,
MaxYear + IterNo() AS Year,
RangeAvg(Peek('Amount', -1, 'FactTable'), Peek('Amount', -2, 'FactTable')) AS Amount
Resident TempYears
While IterNo() <= 2; // Add 2 future years

DROP Table TempYears;