topic Urgent Help Please | Forecasting data in App Development

Urgent Help Please | Forecasting data

Qlik1_User1 — Wed, 26 Feb 2025 10:59:26 GMT

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

Re: Urgent Help Please | Forecasting data

JandreKillianRIC — Wed, 26 Feb 2025 12:35:52 GMT

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

Re: Urgent Help Please | Forecasting data

diegozecchini — Wed, 26 Feb 2025 15:10:55 GMT

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;