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.