Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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?
Many thanks
Regards
Janne
Hi Janne,
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.
HTH
Best regards,
DataNibbler
You could load the data into separate columns in the load script.
if(Plan='B', Sales as Sales_Budget,
if(Plan='F', Sales as Sales_Forecast,
if(Plan='D', Sales as Sales_Demand,
Then you can simply sum the columns to get the values you want, and do comparisons between values to get the variance.
e.g. sum(Sales_Demand - Sales_Forecast)
sum({<Year={2014}>} Sales_Budget) will give the figures for the current year.
sum({<Year={2013}>} Sales_Budget) will give the figures for the last year.
I use the extract same approach data nibbler