Before function on Pivot Table not working

[j_nlucas]

Hi,

I've been looking in the community about this issue but nothing I found worked, so here I am. 

I'm working on a Pivot table for stock projection over the next months. This table starts with the current stock, and based on the orders, forecast, etc, at the end of each month there will be a new amount that will be passed on to the next month as inital amount and so forth. 

The expression I'm using to calculate the stock at the end of the month is rather complex, since many conditions are taken into account, then, for the calculation of the initial stock for the next month I use the "Before" function to read the final stock figure for the previous month.

This "Before" function works well for most months and products, but sometimes and I guess randomly (haven't found a pattern) it won't work, therefore giving the following months a wrong figure as well. 

Then, when I select a specific product, it will work always

The expression for Stock Actual (the one failing) is:

If(Mes=MonthName(vToday),Sum(ProductStock),RangeSum(Before([Stock Prev.]),0))

>>> note that [Stock Prev.] is the name of an expression, not a field

The expression for Stock Prev is attached

Any idea to solve this? 

Thank you in advance

[jaibau1993]

Hi!

Can you share a sample app that reproduces this behaviour? It is quite complex to figure out what is actually happening there since Stock Prev uses "before" functions too. I think the problem may be due to before(Expression with more before's) but it is just a guess.

Regards!

Jaime.

[j_nlucas]

Hi Jaime,

I just left a qvw attached to the post.

Regards

[jaibau1993]

Hi!

I bet the problem radicates in your expressions complexity. Stock Prev. is a recursive expression and Stock Actual is a before of a recursive expression... it definetely brokes my mind (not conceptualy but on how QlikView maneges so)

BUT, you can avoid the before in Stock Actual. I used the following expression for Stock Actual:

If(Mes=MonthName(vToday),Sum(ProductStock),

	If(Left(ItemNo,2)='1D',        
	
		if(sum(KilosPdtes) < sum(KilosFC),
			Column(7)+Column(2)-Column(5),
		    Column(7)+Column(4)-Column(5))
	,
                                                
	If(Left(ItemNo,2)='1C',                           
    	'whatever'
	))
)  

And I obtained the following output:

The problem is apparently solved... except for Stock Prev. In the qvw file you attached Stock Prev. is wrong in some way. In simple words, in jul 2020 it should be 442.961 - 11.712 + 0 and it is definetly not -4.030. But 7.682 ("wrong" Stock Actual) - 11.712 + 0 is, indeed, -4.030. Thus, I bet the problem is actually in Stock Prev. Try to figure it out! (you know better how to build it, i tried to solve Stock Prev. too but I could not).

Regards!

Jaime.

[j_nlucas]

Hi Jaime,

Thanks for your reply. I tried that but unfortunately it also fails. 

In your image attached, you can appreciate it in the first product 1DD001. August is taking a different stock than the one in July. 

Regards,

[jaibau1993]

Hi!

Note that it is not "Stock Actual" what is failing in 1DD001 on August, it is Stock Prev. on July actually.

Regards,

Jaime.

[j_nlucas]

Hi Jaime,

Thanks for pointing that out. You have definitely been quite helpful. Thanks a lot.

Joaquin