Latest version of Production planning forecast

[sogloqlik]

Hi.

Every week we pull from our SAP system the current version of our production forecast and add it to the existing data.

The forecast is always onward.

I have a data table (see attached example). It consists of the following:

• planning date (The date when the product is to be manufactured)
• Version Date (the date when the production forecast version was taken).
• The product
• the planned Qty.

This table is connected to a calendar with planning date as a key.

the need is to show for every calendar week the forecast qty of the latest relevant version (For example: for week fifty-one it will show the data of the version taken in week fifty, for week fifty it will show the data of the version taken in week forty-nine and so forth.

The final is outcome supposed to be a pivot table with

• two dimensions: Year and week 
• One measure: sum of qty.

Any ideas?

[pedrobergo · Accepted solution]

Hi @sogloqlik 

You must use FirstSortedValue to retrieve the latest Version Date from each Product and Planned Date. Using your example table, the formula will be:

Sum( Aggr( FirstSortedValue(Qty,-[Version Date]), Product, [Planned date]) )

[],

Pedro

[sogloqlik]

Thx.