Sum with IF statement inflates total

[#1]

I'm trying to use sum(FIELD_COUNTER) instead of COUNT(DISTINCT FIELD) for efficiency reasons, but when there is an IF statement within the SUM(), the result i get is greatly inflated.  I'm sure there's something I'm just missing or don't know about using IF with fields from 2 different tables, but maybe someone can help fill me in?

The attached doc shows the progression of expressions I've tried.  Count(DISTINCT APPL_NUM) and SUM(APPL_FG) are the same, as expected.  When I add the IF statement to the Count Distinct expression (In Yellow), it looks right, somewhat lower than the result before the IF statement.  But in the last field where I add the same IF statement to the SUM(APPL_FG) expression, the results are much higher than before.

I appreciate any guidance.  Thanks.

[swuehl]

Your sample file is a bit large for me at the moment, but in general, what you could be explained by the QV calculation engine:

The Calculation Engine

If you are using fields from different tables with an aggregation, QV needs to perform a JOINed temporary table on the fly. As you know, a JOIN may duplicate records, this may explain already the inflated Sum.

Besides this, maybe this is of interest:

A Myth About Count(distinct …)

[#3]

Sorry about that, I didn't realize the file i posted was so large.  Here's a more reasonable sized sample doc.

I see where those links you provided explain why I see inflated sums, but I'm still not sure how to resolve without going back to the less-efficient Count Distinct.  Can you help me with that?

[#4]

OK, i read through the Count Distinct info you linked, and that helps, I don't need to change the equation from Count distinct for efficiency's sake.

However, I have another field that does need to be summed (an amount) and I'm having the duplication issue.  Is there a workaround?  I can do SUM(AGGR(AVG(IF(APPL_RECD_DT > Campaign Start Dt, ANNL_AMT)),DOC_ID)) but that seems really resource heavy and i'm still not sure it gives the right result.