# 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

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

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

Not applicable

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

Ok I will give it a go many thanks