Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Anlyze actual vs forecast data different versions, best way?

Hi everyone,

The case evolves around facilities and their consumption. In this case there are around 13000 facilities which equals to 156 000 rows/year which during 5 years results in 780 000 (5*156 000). So each facility should be forecasted every month.

Facilities:1300 rows (per facility) - information about the facility

Forecast: 780 000 rows (per month) - information about the production of the faciltiy

Historical (Actual): 80 000 (normalized table with both facility information and its measures)

Facility

Forecast

Historical

Installation_ID

Version

Start_Date,

End_Date,

Installation_Status,

Customer_ID

Installation_ID

Version

Month Forecasted

Consumption

Installation_ID

Installations_status

Customer_ID

Version

Month Forecasted

Consumption

Ive tried to join the facility table and the forecasted values and then concatenate it with the historical table but it ended up eating all my memory and takes very long time for this one simple left join (32 GB memory installed, QV version 11.2 SR12).

I have tried to approach this in a different way also. I created a link table between these different tables but it didnt give me the result I wanted.

The forecast is generated 5 years in advance and historical values (actual) is a five years back. The business want to analyze each version of the forecast to the previous one and also compare it with historical values (actual).

My questions is how to best approach this? With regards to the datamodell?