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.
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.
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?
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.
