Discussion board where members can get started with QlikView.
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:
The logic goes like this:
IF Flag = ' '
IF Fees > Allowance
AND SUM(Excess) > 0
AND SUM(Excess) > Fees - Allowance
THEN Excess_Allocation = Fees - Allowance
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'
Thanks for your help!
Can you try this
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.
View solution in original post
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.
Its an Expression
The columns all hold numbers really, except Flag, which uses either an x or a ''.
Ok I will give it a go many thanks