topic Re: Assistance with folmula in App Development

Assistance with folmula

GregRyder — Wed, 23 Jul 2025 07:34:33 GMT

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?

Re: Assistance with folmula

GregRyder — Wed, 23 Jul 2025 07:45:10 GMT

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

Re: Assistance with folmula

adilio_silva — Wed, 23 Jul 2025 14:24:37 GMT

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) )

Re: Assistance with folmula

anat — Wed, 23 Jul 2025 17:34:45 GMT

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

Re: Assistance with folmula

SofiaJone — Thu, 24 Jul 2025 05:48:08 GMT

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.

Re: Assistance with folmula

siyrejames12 — Wed, 17 Jun 2026 04:29:59 GMT

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.