topic PEEK function in QlikView

PEEK function

Mon, 17 Feb 2014 13:53:35 GMT

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

Re: PEEK function

datanibbler — Mon, 17 Feb 2014 14:01:37 GMT

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

Re: PEEK function

Colin-Albert — Mon, 17 Feb 2014 14:03:26 GMT

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.

Re: PEEK function

Mon, 17 Feb 2014 14:06:59 GMT

I use the extract same approach data nibbler