Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
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

Re: Need help with an even more complex nested IF statement

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

Re: Need help with an even more complex nested IF statement

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

Re: Need help with an even more complex nested IF statement

Its an Expression

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

Not applicable

Re: Need help with an even more complex nested IF statement

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

Not applicable

Re: Need help with an even more complex nested IF statement

Ok I will give it a go many thanks