topic Re: Sum with IF statement inflates total in QlikView

Sum with IF statement inflates total

Tue, 17 Nov 2015 21:18:14 GMT

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.

Re: Sum with IF statement inflates total

swuehl — Tue, 17 Nov 2015 21:51:20 GMT

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

Re: Sum with IF statement inflates total

Tue, 17 Nov 2015 22:00:09 GMT

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?

Re: Sum with IF statement inflates total

Wed, 18 Nov 2015 01:00:16 GMT

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.