topic Re: Issue creating rolling forecast table in App Development

Issue creating rolling forecast table

bsharma_velcro — Tue, 28 Oct 2025 11:41:47 GMT

Hi,

I am trying to create a table like below, where Actuals and Forecasts are shown in the same table. Users will be able to compare forecasts made in different months with the Actual numbers and compare which of the multiple forecasts was more accurate.

Appreciate any help with how to build the model to achieve this. I have attached sample data used in creating this table.

Regards,

Re: Issue creating rolling forecast table

Daniel_Castella — Tue, 28 Oct 2025 15:01:08 GMT

I got the output doing the following.

BACKEND:

A:
LOAD
"Forecast Creation Month" ,
"Forecast For Month",
"Forecast Value"
FROM [lib://DataFiles/Rolling FC.xlsx]
(ooxml, embedded labels, table is Forecast)
Where Year("Forecast For Month")='2025';

B:
LOAD
"Month",
Actual
FROM [lib://DataFiles/Rolling FC.xlsx]
(ooxml, embedded labels, table is Actual);

C:
NoConcatenate
LOAD "Forecast Creation Month"
RESIDENT A;

Outer Join (C)
LOAD
"Month" as "Forecast For Month",
Actual
RESIDENT B;

TempMax:
LOAD AddMonths(Max(Month),1) as Month
RESIDENT B
Where len(Actual)>0;

LET vMaxMonth = Peek('Month', 0, 'TempMax');

DROP TABLE TempMax;

Concatenate (A)
LOAD *
RESIDENT C;

E:
NoConcatenate
LOAD *
RESIDENT A
Where "Forecast Creation Month"<='$(vMaxMonth)';

DROP TABLE A, B, C;

FRONT END:

Pivot table with

-Row: [Forecast Creation Month]

-Column: [Forecast For Month]

-Measure: IF([Forecast Creation Month]<=[Forecast For Month],Sum([Forecast Value]),Sum(Distinct Actual))

-Background Color expression: IF([Forecast Creation Month]<=[Forecast For Month], RGB(100,100,255), RGB(255,255,0))

Let me know if it works for you. The colors are not exactly the same, but you can adjust it with the RGB numbers. Note also that, since the last two rows in your output are empty, they will not appear in Qlik Sense table.

Kind Regards

Daniel

Re: Issue creating rolling forecast table

bsharma_velcro — Tue, 28 Oct 2025 15:53:49 GMT

Thanks Daniel. This is working as expected 🙂

Regards,

Re: Issue creating rolling forecast table

bsharma_velcro — Thu, 06 Nov 2025 13:30:01 GMT

Hi @Daniel_Castella,

The issue got a bit complex after throwing in some additional dimensions in those two tables. Could you please review the attached file and suggest how should I achieve the same output with these fresh set of fields in the input tables.

Regards,

Re: Issue creating rolling forecast table

Daniel_Castella — Thu, 06 Nov 2025 16:36:57 GMT

Hi @bsharma_velcro

Try this code. If I did it correctly, you should be able to use the same FrontEnd Pivot Table that I posted in the other solution without needing to modify it.

A:
LOAD
Date(Date#(Month("Forecast Date")&'-'&Year("Forecast Date"), 'MMM-YYYY'), 'MMM-YYYY') as "Forecast Creation Month",
"Item Code",
"Area Code",
"Account Code",
Date(Date#(pick(Month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')&'-'&"Year", 'MMM-YYYY'), 'MMM-YYYY') as "Forecast For Month",
Forecast
FROM [lib://DataFiles/Data and Output.xlsx]
(ooxml, embedded labels, table is Forecast);

B1:
LOAD
"Item Code",
"Area Code",
"Account Code",
Date(Date#(Month("Transaction Date")&'-'&Year("Transaction Date"), 'MMM-YYYY'), 'MMM-YYYY') as "Forecast For Month",
Actual
FROM [lib://DataFiles/Data and Output.xlsx]
(ooxml, embedded labels, table is Actual);

B:
NoConcatenate
LOAD
"Forecast For Month",
sum(Actual) as Actual
RESIDENT B1
GROUP BY "Forecast For Month";

DROP TABLE B1;

C:
NoConcatenate
LOAD "Forecast Creation Month"
RESIDENT A;

Outer Join (C)
LOAD
"Forecast For Month",
Actual
RESIDENT B;

TempMax:
LOAD AddMonths(Max("Forecast For Month"),1) as "Month"
RESIDENT B
Where len(Actual)>0;
;
LET vMaxMonth = Peek('Month', 0, 'TempMax');

DROP TABLE TempMax;

Concatenate (A)
LOAD *
RESIDENT C;

E:
NoConcatenate
LOAD "Forecast Creation Month" ,
"Forecast For Month",
"Item Code",
"Area Code",
"Account Code",
If("Forecast Creation Month"<='$(vMaxMonth)',"Forecast",'-') as "Forecast",
If("Forecast Creation Month"<='$(vMaxMonth)',"Actual",'-') as "Actual"
RESIDENT A
Where "Forecast Creation Month"<='$(vMaxMonth)';

DROP TABLE A, B, C;

With it I obtain the following table. Let me know if it works for you.

Kind Regards

Daniel

Re: Issue creating rolling forecast table

bsharma_velcro — Thu, 06 Nov 2025 17:39:26 GMT

Thanks @Daniel_Castella.

I have a concern regarding the measure:

-Measure: IF([Forecast Creation Month]<=[Forecast For Month],Sum([Forecast Value]),Sum(Distinct Actual))

Will using Distinct while summing up Actual not create issue if we have same Actual numbers repeating multiple times in the data? Will it not impact the outcome by reducing the total for Actuals?

Regards,

Re: Issue creating rolling forecast table

bsharma_velcro — Thu, 06 Nov 2025 17:41:46 GMT

Also, it won't allow me to apply filter by dimensions like Item, Area and Account codes.

Re: Issue creating rolling forecast table

Daniel_Castella — Fri, 07 Nov 2025 14:40:27 GMT

Hi @bsharma_velcro

Ok, it was not specified that the chart needed to react to the fields but seems reasonable. I have improved the code:

LOAD

Date(Date#(Month("Forecast Date")&'-'&Year("Forecast Date"), 'MMM-YYYY'), 'MMM-YYYY') as "Forecast Creation Month",

"Item Code",

"Area Code",

"Account Code",

Date(Date#(pick(Month,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')&'-'&"Year", 'MMM-YYYY'), 'MMM-YYYY') as "Forecast For Month",

Forecast

FROM [lib://DataFiles/Data and Output.xlsx]

(ooxml, embedded labels, table is Forecast);

B1:

LOAD

"Item Code",

"Area Code",

"Account Code",

Date(Date#(Month("Transaction Date")&'-'&Year("Transaction Date"), 'MMM-YYYY'), 'MMM-YYYY') as "Forecast For Month",

Actual

FROM [lib://DataFiles/Data and Output.xlsx]

(ooxml, embedded labels, table is Actual);

NoConcatenate

LOAD

"Item Code",

"Area Code",

"Account Code",

"Forecast For Month",

Sum(Actual) as Actual

RESIDENT B1

GROUP BY "Item Code",

"Area Code",

"Account Code",

"Forecast For Month";

DROP TABLE B1;

NoConcatenate

LOAD Distinct "Forecast Creation Month"

RESIDENT A;

Outer Join (C)

LOAD

"Item Code",

"Area Code",

"Account Code",

"Forecast For Month",

Actual

RESIDENT B;

TempMax:

LOAD AddMonths(Max("Forecast For Month"),1) as "Month"

RESIDENT B

Where len(Actual)>0;

LET vMaxMonth = Peek('Month', 0, 'TempMax');

DROP TABLE TempMax;

Concatenate (A)

LOAD *

RESIDENT C

Where "Forecast Creation Month">"Forecast For Month";

NoConcatenate

LOAD

"Forecast Creation Month",

"Forecast For Month",

"Item Code",

"Area Code",

"Account Code",

If("Forecast Creation Month"<='$(vMaxMonth)',"Forecast",'-') as "Forecast",

If("Forecast Creation Month"<='$(vMaxMonth)',"Actual",'-') as "Actual"

RESIDENT A

Where "Forecast Creation Month"<='$(vMaxMonth)';

DROP TABLE A, B, C;

Also, in the pivot table you need to change the measure to this expression:

IF([Forecast Creation Month]<=[Forecast For Month],Sum([Forecast]),Sum(TOTAL<[Forecast For Month]> Actual)/
Count(TOTAL <[Forecast For Month]> Distinct If([Forecast Creation Month]>[Forecast For Month],[Forecast Creation Month])))

This will make the values to react to the fields' selection. Moreover, I deleted that Sum Distinct that could cause issues (I know there is a Count Distinct now, but it is just a month counter needed to avoid counting duplicates caused by the months join).

Kind Regards

Daniel