Can I make a recursive calculation, where each result is used in the following calculation?

[Nickels]

Hi, you heroes of the day

I have this issue that I can't seem to solve myself. The following is an example on what I hope to be able to accomplish:

• I have the periods 1 to 10.
• For the period 1 to 5, column A contains some values. Consider the values to be sales of something, and note that the sales are decreasing by each period.
• For the period 1 to 10, column B also contains some values, which also decreases by each period.
• Column B_delta is the percentage change from the current period relative to the period right before it.
• I can calculate the percentage change for column A for the periods 1 to 5, but I want to be able to forecast the sales of A for the remainging periods 5 to 10. Since I only have B to relate to, I want to apply the percentage change in B to a forecast of A (I know this is not a very good forecast, but bear with me 🙂 ). So I want to calculate the numbers marked with green below.

I hope the issue is somewhat clear.

I would like to be able to do something like this for each period from 5 to 10:

A_forecast = Above(Sum(A),1,1)*B_delta

Hence, I need some kind of recursive calculation, where the result of the previous calculation lays the foundation for the next calculaton. Or do I? How am I going to solve this?

NB: Columns A and B are filter-based, with a selection of hundreds of possibilities, and they will be changed as the users feel like. The calculations need to be dynamic and valid for each possible selection. So I assume, that I can't solve it in the load statement.

[anushree1]

could you please elaborate on how you are getting A_forecast for period from 6 to 10 with an example 

[Nickels]

Hi Anushree1

Sure thing. And thank you for replying.

I have made it in Excel like this:

So I take the value of A from the previous period and multiplies it with the percentage change in B for the current period. This result I consider to be a forecast of A in the current period. And I then use this result in the following calculation by multiplying it with the percentage change in B in the following period. This result I then consider to be the forecast of the following period, etc.

[kaushiknsolanki]

Try this, Data:

Data:
LOAD * INLINE [
A, B, C
1, 100, 100
2, 80, 90
3, 60, 80
4, 40, 70
5, 20, 60
6, , 50
7, , 40
8, , 30
9, , 20
10, , 10
];

Data1:
Load *,If(B=0 or IsNull(B) or B='',peek(NewValue)*Percentage,B) as NewValue;
Load *,C/Previous(C) as Percentage
Resident Data;

Drop table Data;

Regards,

Kaushik Solanki

[anushree1]

Please check if the attached file works

[Nickels · Accepted solution]

Hi everyone

It turned out that this, when you know it rather simple, piece of code did the job 🙂

A_forecast =

Rangesum(above(SUM(A),0,rowno()),0)
+
Rangesum(above(
RangeSum(above(
Sum({<[Period]=Max([Period])>}A)
,1,rowno()))

*decay-factor //This piece of the code is left out intentionally, since it is independent of the problem at hand.

,0,rowno()),0)

Thank you everyone...

[DropTable]

Hi,

Is it possible to do recursive calculation in charts?

vFore = sum(sales)-above($(vFore),0,1)

This generates infinit lines of variable expressions in down below at the validation part of the variables page, (if you did not manage to crush the qlik sense in the meantime). 
I am simply trying to create exponential smoothing, and I want users to change the alpha value, which I plan to make it as a prompt. But not sure if its doable in qlik sense. 

Please let me know if you have any practice, 

Thanks.