Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Contributor III

## Help with previous() and peek() to determine new calculated values

Assume the following situation where some users have a budget to spend, and an increase of their budget after some time. Now the user can spend some of his budget or not, but we need to determine their current budget after a purchase and/or increase.

An example is given in the image. Specifically the green highlighted section is not working properly at this moment.

For each month I determine the current starting budget of that specific month, and the final budget at the end of the month. Hence columns L and M.

The problem I'm facing is that the MonthsEndBudget is not working properly in displaying the data in the first record, i.e. 90 for UserId 1 in jan 2020. And second, when there is a budget increase, column F orange highlighted, is not added to the MonthsEndBudget in january 2021. At which we also see an increment of +1 in column G (light blue).

The not functioning properly formula used to determine MonthsEndBudget is: If(peek(BudgetId,-1) = BudgetId,If(IsNull(TransactionAmount),Peek('MonthsEndBudget'),CurrentBudget-TransactionAmount)) As MonthsEndBudget

And to determine MonthsStartBudget:

If(BudgetId = Previous(BudgetId), If(IsNull(TransactionAmount), Peek(MonthsEndBudget),Previous(MonthsEndBudget)), 0) as MonthsStartBudget

Can someone guide me into the right direction to determine the MonthsStartBudget and MonthsEndBudget properly, as displayed in the image?

Also check attachment for the dummy data.

Many thanks

Labels (4)

• ### Scripting and Expressions

1 Solution

Accepted Solutions
Creator III

Try this:

[sheet2]:
NoConcatenate
Alt(if(BudgetId = Previous(BudgetId),Alt(MonthsStartBudget_Cal,0),0)
+ if(IsNull(BudgetIncrease),0,BudgetIncrease)
- if(IsNull(TransactionAmount),0,TransactionAmount),
Peek(MonthsEndBudget_Cal)) as MonthsEndBudget_Cal;
*,
if(BudgetId = Previous(BudgetId), Alt(Peek(MonthsStartBudget_Cal),0) + Alt(Previous(BudgetIncrease),0) - Alt(Previous(TransactionAmount),0) , 0) as MonthsStartBudget_Cal

Resident Sheet1;

The idea is we get the MonthsStartBudget first, then use it to calculate MonthsEndBudget. Peek is used to get value from the output and Previous is used to get value from the input. FYR.

Creator III

Try this:

[sheet2]:
NoConcatenate
Alt(if(BudgetId = Previous(BudgetId),Alt(MonthsStartBudget_Cal,0),0)
+ if(IsNull(BudgetIncrease),0,BudgetIncrease)
- if(IsNull(TransactionAmount),0,TransactionAmount),
Peek(MonthsEndBudget_Cal)) as MonthsEndBudget_Cal;