topic Re: Before function on Pivot Table not working in QlikView

Before function on Pivot Table not working

j_nlucas — Sat, 16 Nov 2024 19:20:38 GMT

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

Re: Before function on Pivot Table not working

jaibau1993 — Thu, 09 Jan 2020 08:10:31 GMT

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.

Re: Before function on Pivot Table not working

j_nlucas — Thu, 09 Jan 2020 11:06:27 GMT

Hi Jaime,

I just left a qvw attached to the post.

Regards

Re: Before function on Pivot Table not working

jaibau1993 — Thu, 09 Jan 2020 12:16:08 GMT

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.

Re: Before function on Pivot Table not working

j_nlucas — Thu, 09 Jan 2020 17:37:38 GMT

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,

Re: Before function on Pivot Table not working

jaibau1993 — Fri, 10 Jan 2020 07:24:45 GMT

Hi!

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

Regards,

Jaime.

Re: Before function on Pivot Table not working

j_nlucas — Fri, 10 Jan 2020 08:36:04 GMT

Hi Jaime,

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

Joaquin