Assistance with folmula

[GregRyder]

sum(if(TCODE='IN',AMOUNT)) - Sum(if(TCODE='CN',AMOUNT)) - Sum(total if(TCODE='IN', AVRGCOST)) - Sum(if(TCODE='CN',AVRGCOST)) / (Sum(total if(TCODE='IN', AVRGCOST)) - Sum(if(TCODE='CN',AVRGCOST)))

1st Red sum = 2695334

2nd Green Sum = 499956

So 2695334 / 499956 should = 0.53 or 53%

What am I doing wrong?

[GregRyder]

Hi

Dont worry, worked it out, I created master items of the two sums and then did the formula I wanted

[adilio_silva]

You can still add the formula, there was only one parenthesis missing in the entire red block

((sum(if(TCODE='IN',AMOUNT)) - Sum(if(TCODE='CN',AMOUNT)) - Sum(total if(TCODE='IN', AVRGCOST)) - Sum(if(TCODE='CN',AVRGCOST))) / (Sum(total if(TCODE='IN', AVRGCOST)) - Sum(if(TCODE='CN',AVRGCOST)))

I suggest replacing the IFs with Set Analysis for better performance as follows below (other set optimizations are still possible)

(
      Sum({<TCODE={IN}>} AMOUNT) 
    - Sum({<TCODE={CN}>} AMOUNT + AVRGCOST) 
    - Sum({<TCODE={IN}>} Total AVRGCOST)     
) 
/ 
(
      Sum({<TCODE={IN}>} Total AVRGCOST) 
    - Sum({<TCODE={IN}>} AVRGCOST)
)

[anat]

for better performance of the dashboard you can use the Set analysis instead of the IF statement.

[SofiaJone · Accepted solution]

I ran into similar confusion before when working with nested sums and averages like this. One thing to double-check is the order of operations—especially with how parentheses are used around the numerator and denominator.

Sometimes I test these calculations outside Qlik to verify if the math works out as expected. For quick checks, I use this basic calculator tool: lacalculadaradealicia.es — it helps me isolate and validate parts of the formula step by step.

You might want to wrap your denominator in parentheses like this:
Sum(if(TCODE='IN',AMOUNT))
- Sum(if(TCODE='CN',AMOUNT))
- (Sum(total if(TCODE='IN', AVRGCOST)) - Sum(if(TCODE='CN',AVRGCOST)))
/ (Sum(total if(TCODE='IN', AVRGCOST)) - Sum(if(TCODE='CN',AVRGCOST)))
That way, you're not dividing before completing the subtraction. Let me know if that helps.