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.

[siyrejames12]

The issue is likely operator precedence. In your formula, only the last term may be getting divided by the denominator instead of the entire numerator. If you want the percentage based on the difference between the first two sums divided by the difference between the AVRGCOST sums, use parentheses: you can use this https://lacalculadoradealiciaes.es/ type of calculator site to do some calculations.

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

Also, note that 2695334 ÷ 499956 ≈ 5.39, which equals 539%, not 53%. If you expect 53%, double-check the values being used in the calculation or whether a decimal place is missing.