3 Replies Latest reply: Nov 17, 2015 8:00 PM by Ashley Casselman RSS

    Sum with IF statement inflates total

    Ashley Casselman

      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.