Compare actual data and several versions of monthly forecast data

[arnoqlik]

Hello,

I have Excel files as a source of my application.

All files include sales data but some for actual and others for forecast.
Each month I have an Excel file with the forecast of the next months (field MonthForecast in the attachment) and another file for the actual sales of the previous month.

The months of the forecast and the actual results are linked to a calendar table through a key field (format 'YYYYMMDD').

The aim is to mix both sources of data together. For example:

• If I select MonthForecast = February 2021 (file received in February 2021 for the forecast of the next months) then I should have actual data before February 2021 and the forecast starting from February 2021.

• If I select MonthForecast = March 2021 (file received in March 2021 for the forecast of the next months) then I should have actual data before March 2021 and the forecast starting from March 2021.

I would write it like that: If Period_Month < MonthForecast Then AmountActual Else AmountForecast

As you can see in the attachment, the actual data is not linked to the MonthForecast so I can't write this formula.

Thank you in advance for your help!

Note: the title may be confusing so I hope that the explanation helps to understand the problem!
Managing 2 calendars would be a solution?

Note 2: the source files 'Actual' and 'Forecast' have a lot of common fields, that's why I merged everything into one single table.

Note 3: I will have to create a chart too that shows actual/forecast data for the previous period.
Example: I select Period_Month = February-June 2021 and MonthForecast = March 2021 (so actual data for February 2021 and forecast data for March-June 2021). I'd like to have the actual data for February-June 2020.

Note4: I'm a newbie in Qlikview dev

[juleshartley]

You could create a variable called vMFcst which = Max(MonthForecast)

Then have Period_Month as dimension and the below as expression.

=sum({1<Period_Month={"<$(vMFcst)"}>}AmountActual) 
+
sum({<Period_Month={">=$(vMFcst)"}>}AmountForecast)

However... I wouldn't recommend this as an approach due to the '1' in the set analysis, which will ignore other selections. Better would be to create a new floating table with the ForecastMonth field and then use this to select the month and feed to the variable.

[arnoqlik]

Thanks but it doesn't really answer to the problem.

MonthForecast has to be set as a column in addition to Calendar which is on the left.

Each month, I receive new files with new forecast data so I'd like to compare them.

I attach an Excel file to show you what I need for the restitution.