I am not very good with peek-formula, so I would need some help.
I have a table in where I have all sales forecasts, budgets and demandplans in same table. I know how to write the where clause that I can limit my load only for this years budget, but how about the latest forecast and last and previous demand plan?
Plan
Year
Version
Sales
B
2014
1
200
F
2014
3
300
F
2014
4
300
D
2014
3
100
D
2014
2
100
B= budget
F=forecast
D=demand plan
I have an example above. When I want to compare actuals to budget, I need to compare actuals to first row and that I can limit by saing when B then only the current year. When I would like to compare actuals to forecast I would need to have the current year and the latest version (in this case version 4); how can I write the forecast limit so that only the latest is taken into account? Same thing with demand plan, but I would need to have also the previous plan (so called N-1); how can I do that? What script I need to write that I see the current demand plan as D, 2014, version 3 and the N-1 plan as D, 2014, version 3?
if you need only the latest version of the forecast, but the latest and the one before of the demand_plan,
then you can't do it in one LOAD statement - well, you could by using a rather complex IF_construct, but I'd suggest otherwise - to keep it simple and easy_to_understand for others, should you not be there.
=> Try implementing in the LOAD an aggregation (GROUP BY) and a max() function,
combined with a SORT BY (do it in two steps for the reason stated above) (max(version) is an aggregation function and requires that you use a GROUP BY clause encompassing all other (non-aggregated) field.
=> By sorting in a way so that all the lines with the same letter will be in one block, you use max() to load the one with the largest version_nr. and finally, with a WHERE... clause (at the end of the LOAD) you filter for all the letters except D (for the demand plan, you have to use a modified max() formula to load the largest and second-largest version_nr.
