topic Re: Stock calculation with nested IFs in App Development

Stock calculation with nested IFs

valpassos — Sat, 16 Nov 2024 05:53:22 GMT

Hi community,

I have the following piece of code:

IF(ID_Company=Previous(ID_Company),
IF(Reference=Previous(Reference),
IF(Warehouse=Previous(Warehouse),
Peek("Final Stock")-Peek("Stock Flows"),
"Final Stock"),
"Final Stock"),
"Final Stock") AS "Final Stock",

IF(ID_Company=Previous(ID_Company),
IF(Reference=Previous(Reference),
IF(Warehouse=Previous(Warehouse),
Peek("Final Stock")-Peek("Stock Flows"),
"Final Stock"),
"Final Stock"),
"Final Stock") - "Stock Flows" AS "Initial Stock"

Could someone please explain to me, in verbose mode, what this piece of script is doing?
I know it's calculating the Initial Stock based on Final Stock, but I don't entirely understand the if-else logic. There is way too else conditions.
For instance: there is a else condition "Final Stock" for each IF, but what is this doing, functionally?

Thanks in advance!

Re: Stock calculation with nested IFs

JordyWegman — Thu, 09 May 2019 13:52:32 GMT

HI Valpassos,

What it is doing is as follows:

IF(ID_Company=Previous(ID_Company), // it will check, on the row, if the company name on that row is the same as the company name on the previous row. Say, on row 10 you have company XYZ, it will check if on row 9, the company is also called XYZ. If not, it will return the Final Stock. IF(Reference=Previous(Reference), For this part the same, as it will check the reference. IF(Warehouse=Previous(Warehouse), Then also for this part where it will check the warehouse name. Peek("Final Stock")-Peek("Stock Flows"), Now it will check with peek for the result of this function, called Final Stock. It will now subtract the Stock Flows. "Final Stock"), If the warehouse name is not the same, it will return the Final Stock. "Final Stock"), If the reference name is not the same, it will return the Final Stock. "Final Stock") If the ID_Company name is not the same, it will return the Final Stock. AS "Final Stock"

Jordy

Climber

Re: Stock calculation with nested IFs

valpassos — Thu, 09 May 2019 16:04:48 GMT

Exactly I was looking for!

Thanks, @JordyWegman!

Re: Stock calculation with nested IFs

valpassos — Mon, 20 May 2019 11:02:15 GMT

[REOPENED]

Hi!

I have still another question to make regarding this issue.

We have this structure:

When we do an IF for each condition, do all the 3 IFs need to happen (intersection) in order to do the peek()?

Thanks once again community,

Lisa

Re: Stock calculation with nested IFs

JordyWegman — Mon, 20 May 2019 11:23:21 GMT

Hi Lisa,

This would result in the same answer. So you can use both if you want. And yes, all happen in that order, but also for your 'AND' function.

Jordy

Climber

Re: Stock calculation with nested IFs

valpassos — Mon, 20 May 2019 13:47:11 GMT

Alright!

Many thanks for the prompt reply, @JordyWegman 🙂

Lisa