calculate result depends on results in upper row

[jacekp]

Hi Qlik Masters,

I am facing some calculation problem with calculation that it is easy to do in excel. 

We have month, stock, avg_cons. Result is requested result we would like to see at the end.

We can use stock only till the month that exists. Example we can use stock from month 1 only in month 1.  Stock from month 11 can be used till 11 month include.

How we calculate:

for the first month (first row) it is simly: stock - avg_cons

2nd line: we check if results from upper month is bigger than 0, if yes, then stock(current line) - avg_cons(current line), if not it is: result from the upper month + stock(current line) - avg_stock(current line)

month	stock	avg_cons	result
1	9227	836.25	8390.75
2		836.25	-836.25
3		836.25	-1672.5
4		836.25	-2508.75
5		836.25	-3345
6		836.25	-4181.25
7		836.25	-5017.5
8		836.25	-5853.75
9		836.25	-6690
10		836.25	-7526.25
11	10890	836.25	2527.5
12		836.25	-836.25
13	5045	836.25	3372.5

The problem I have is to somehow define in formula range of avg_cons that needs to be taken for calculation.

Any idea how to deal with it?

if(

above(
sum(aggr(Sum(
{<Dataview = {"Stock"}
>}
Value),YearMonth))
-
rangesum(above(Sum({<Dataview = {"consumption"}>}Value),0,rowno()))
)
>0,

sum(aggr(Sum(
{<Dataview = {"Stock"}
>}
Value),YearMonth))
-
rangesum(above(Sum({<Dataview = {"consumption"}>}Value),rowno()-1,rowno()))
,

sum(aggr(Sum(
{<Dataview = {"Stock"}
>}
Value),YearMonth))
-
rangesum(above(Sum({<Dataview = {"consumption"}>}Value),0,rowno()))
)

[Oleg_Troyansky]

Hi there!

I think you are almost there, but I'd recommend to include all of the logic inside the AGGR() function, not outside of it. So, the overall formula should look like this:

sum(

     AGGR(

           IF( Above( ...) >0, ...., .....)

           ,

          YearMonth

     )

)

I'm teaching advanced uses of Set Analysis and AGGR() at the Masters Summit for Qlik  and my colleagues Rob Wunderlich, Barry Harmsen, Bill Lay and Nick Webster teach many other advanced development techniques. Check out our agenda - we are coming soon to Orlando and to Dublin!

[jacekp]

if I include formula inside the aggr funtion nothing has changed

it returns, as initial

1	8390.75
2	-836.25
3	-2508.75
4	-3345
5	-4181.25
6	-5017.5
7	-5853.75
8	-6690
9	-7526.25
10	-8362.5
11	1691.25
12	-836.25
13	-5826.25

[Oleg_Troyansky]

I don't quite understand what's the problem and what are you trying to achieve. Based on your description of the logic, your formula works exactly as expected. What are you trying to do differently?

[jacekp]

Please check 3rd line, should be -1672 not -2508. I have -2503 because it calculates 3x836 white 2x836 is correct. It shoudl start sum up avg_cons from 2nd line as result of upper row is positive

[Oleg_Troyansky]

I see... Maybe this is too much to be calculated in a chart expression - perhaps you move at least some of these calculations to the data load script? 

Also, I have a feeling that the approach is overly complicated. Can you formulate the business logic behind all these calculations? What are you trying to calculate, in business terms?

[jacekp]

Any suggestion how divide it for the script and formula?

Let's say that stock and month presents exipry date and we can't use it next month. While the demand is 836per month. In first month then stock - demand shows scrapping. Starting from the second month you can only use stock with exipry date of 11.

[Oleg_Troyansky]

I'm describing a similar process in a lot of detail in my book QlikView Your Business, in a chapter devoted to Inventory Analysis. I encourage you to check it out.

In a few words, you will need to use functions Peek() and Previous() to calculate cumulative balances. You will need to load your data sorted chronologically. For each inventory balance, you'd check the expiration date and calculate useful stock and waste.

You can find several blog articles about Peek() and Previous(), including my own.

Best,