Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
j_nlucas
Partner - Contributor III
Partner - Contributor III

Before function on Pivot Table not working

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

 

capture.png

Any idea to solve this? 

 

Thank you in advance

 

 

Labels (2)
6 Replies
jaibau1993
Partner - Creator III
Partner - Creator III

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
Partner - Contributor III
Partner - Contributor III
Author

Hi Jaime,

I just left a qvw attached to the post.

Regards

jaibau1993
Partner - Creator III
Partner - Creator III

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:

before.png

 

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
Partner - Contributor III
Partner - Contributor III
Author

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
Partner - Creator III
Partner - Creator III

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
Partner - Contributor III
Partner - Contributor III
Author

Hi Jaime,

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

Joaquin