Using peek() or previous in a table Qlikview

[miguelbraga]

Hi people,

I have the following question about using peek() or previous() function to give me a specified result. What I have is something like this:

Week	N_Orders	N_Planned	N_Stock	Total
Week1	500	640	500	-640
Week2	500	640	500	-640
Week3	500	640	500	-640

The total value represents what left to be Planned wich formula is:

Total = N_Orders - N_Planned - N_Stock.

The following result is what I need to make:

Week	N_Orders	N_Planned	N_Stock	Total
Week1	500	640	500	-640
Week2	500	640	0	-140
Week3	500	140	0	360

The Stock need to become with the value 0 because I've just used all the Stock in the Order of the first Week (If in the first week and the following were 250, N_Stock column would be [500, 250, 0]). The Total value in the second Week is replaced by the negative value of the previous Total number.

N_Orders, N_Planned and N_Stock comes from 3 different tables and this numbers are from one single product. In my case I have multiple products.

How can I achieve my goals with qlikview? I've tried different variations of code with no sucess. Please, I need your help! I'll be in touch for explaining further more if needed.

Many Thanks, you guys are the greatest help! 

[marcus_sommer]

Have a look on this: Peek() vs Previous() – When to Use Each

- Marcus

[miguelbraga]

I've read that article and read all about the function peek and previous in the help of Qlikview. After that I still couldn't resolve my problem.

Thanks Marcus.

[marcus_sommer]

I think the issue had not really a relation to peek/previous. Of course you could try it with some nested if loops to fetch all your conditions - but I believe it's more the question how looked the table from where is N_Stock is come from and how will it be matched with the other data.

- Marcus

[miguelbraga · Accepted solution]

Hi Marcus.

I'm sorry for the delay of my response. Here is how I resolved it:

Planeamento + Planear as A_Planear;

Load *,

	if(Necessity < 0, 0, Necessity) as ToPlan;

Load *,

	if
	(
		peek('Product')<>Artigo or isnull(peek('Product')),
		N_Orders - if(isnull(Qty_P_Total_temp), 0, Qty_P_Total_temp) - if(isnull(Qty_S_Total_temp), 0, Qty_S_Total_temp),
		Orders - Planned - Stock
	) as Necessity

Load *,

if(Stock_Rem < 0, 0, Stock_Rem) as Stock;

Load *,

	if
	(
		peek('Product)<>Product or isnull(peek('Product')),
		Qty_P_Total,
		(peek(N_Orders)-peek('Planned')-peek('Stock_Rem))*(-1)
	) as Planned;

Load *,

N_Orders as Orders;

Load *,

	if
	(
		peek('Product')<>Artigo or isnull(peek('Product')),
		Qty_S_Total,
		(peek('Stock_Rem') - peek('N_Orders'))
	) as Stock_Rem;

Many Thanks for the reply it helped