Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor II

Stock calculation with nested IFs

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?

Labels (2)

• Nested If

2 Solutions

Accepted Solutions
Highlighted
Partner

Re: Stock calculation with nested IFs

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

Work smarter, not harder
Highlighted
Partner

Re: Stock calculation with nested IFs

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

Work smarter, not harder
5 Replies
Highlighted
Partner

Re: Stock calculation with nested IFs

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

Work smarter, not harder
Highlighted
Contributor II

Re: Stock calculation with nested IFs

Exactly I was looking for!

Thanks, @JordyWegman!

Highlighted
Contributor II

Re: Stock calculation with nested IFs

[REOPENED]

Hi!

I have still another question to make regarding this issue.

We have this structure:

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"

How different is this IF chain from doing:
IF(ID_Company=Previous(ID_Company)
AND Reference=Previous(Reference)
AND Warehouse=Previous(Warehouse),... ?

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

Highlighted
Partner

Re: Stock calculation with nested IFs

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

Work smarter, not harder
Highlighted
Contributor II

Re: Stock calculation with nested IFs

Alright!

Many thanks for the prompt reply, @JordyWegman  🙂

Lisa