Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Need help with an even more complex nested IF statement

Thought I could pull this off with what Ram just helped me with, but this one had a greater number of outcomes and not sure which to approach first.

The result column I am populating is called Excess_Allocation.

The columns I am using for this are:

Flag

Excess

Allowance

Fees

The logic goes like this:

IF Flag = ' '

     IF Fees > Allowance

          AND SUM(Excess) > 0

          AND SUM(Excess) > Fees - Allowance

     THEN Excess_Allocation = Fees - Allowance

     IF Fees > Allowance

          AND SUM(Excess) > 0

          AND SUM(Excess) < Fees - Allowance

     THEN Excess_Allocation = SUM(Excess) * (Fees - Allowance) / (SUM(Fees) - SUM(Allowance))

     IF Fees < Allowance

     THEN Excess_Allocation = 0

IF Flag = 'x'

     THEN Excess_Allocation = 0

Thanks for your help!

1 Solution

Accepted Solutions
Not applicable
Author

David,

Can you try this

if(Flag='',

  if(Fees > Allowance and sum(Excess) > 0 and sum(Excess) > (Fees-Allowance),Fees-Allowance,

  if(Fees > Allowance and sum(Excess)>0 and sum(Excess) < (Fees-Allowance), ((sum(Excess)*Fees-Allowance)/ (sum(Fees)-sum(Allowance))),

  if (Fees < Allowance, 0)),0),0)

Not sure whether this will work, but with tweaks with the braces you can make it work.

-Ram

View solution in original post

4 Replies
Not applicable
Author

David,

Where you are trying to do at Script level or at the Expression, Can you also give me the sample data file. So i can work and get back to you.

-Ram

Not applicable
Author

Its an Expression

The columns all hold numbers really, except Flag, which uses either an x or a ''.

Not applicable
Author

David,

Can you try this

if(Flag='',

  if(Fees > Allowance and sum(Excess) > 0 and sum(Excess) > (Fees-Allowance),Fees-Allowance,

  if(Fees > Allowance and sum(Excess)>0 and sum(Excess) < (Fees-Allowance), ((sum(Excess)*Fees-Allowance)/ (sum(Fees)-sum(Allowance))),

  if (Fees < Allowance, 0)),0),0)

Not sure whether this will work, but with tweaks with the braces you can make it work.

-Ram

Not applicable
Author

Ok I will give it a go many thanks