Issue creating rolling forecast table

[bsharma_velcro]

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,

BS

[Daniel_Castella · Accepted solution]

Hi

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

[bsharma_velcro]

Thanks Daniel. This is working as expected 🙂

Regards,

BS

[bsharma_velcro]

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,

BS 

[Daniel_Castella]

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

[bsharma_velcro]

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,

BS

[bsharma_velcro]

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