topic Re: Intersection giving wrong results in App Development

Intersection giving wrong results

Federicofkt — Tue, 06 May 2025 14:40:13 GMT

I'm trying to perform an intersection in the set analysis using this formula:

=count( {
<[Company.id] = P({<Year= {'2024'}, TypeF={'REFAZ'}, [Company.Type]={'A'}>})>
*
<[Company.id] = P({<Year= {'2025'}, TypeF={'REFAZ'}, [Company.Type]={'A'}>})>
}
distinct [Company.id])

The formula is returning no errors, hence it is giving me wrong results, since if I use the two measures separated in two columns, I have companies with 1 for both 2024 and 2025 that got 0 in the intersection, and companies with 1 in 2025 and 0 in 2025 that got 1 in the intersection.

What am I doing wrong?

Re: Intersection giving wrong results

agigliotti — Tue, 06 May 2025 15:18:50 GMT

Hi @Federicofkt ,

Could you post a straight table with company id, year, TypeF and company type as dimensions and the two measures?

Re: Intersection giving wrong results

marcus_sommer — Tue, 06 May 2025 16:45:52 GMT

Maybe in this way:

=count({<Year= {'2024'}, TypeF={'REFAZ'}, [Company.Type]={'A'} >
* <Year= {'2025'}, TypeF={'REFAZ'}, [Company.Type]={'A'}>} distinct [Company.id])

Re: Intersection giving wrong results

Oleg_Troyansky — Tue, 06 May 2025 18:03:12 GMT

Hi everyone,

I tested this, and I believe that the issue here is using the ID field for counting. Examine the following screenshot:

- The first column is a simple distinct count of the field Customer (which is an attribute in the Customers table) - 199 customers in total

- The second column is the intersection of possible values of the same field Customer, where the intersection is defined within a single Set Modifier <...> - 174 customers.

- The third column is the same condition, where the intersection is defined across two different modifiers: <...> * <...>, and the result is the same as in column 2 - 174 customers.

- Then, testing the idea suggested by Marcus, I simplified the condition to the intersection of two sets, with just the Year conditions, skipping the Customer filters and functions P() - same result.

- Finally, I modified the same expression, replacing the Customer field with the key field [%Customer ID], and that's when the expression turned to zero.

In conclusion - don't use key fields for counting. You may get ambiguous results, and Qlik intentionally prohibits that.

Cheers,

Re: Intersection giving wrong results

agigliotti — Fri, 09 May 2025 07:33:31 GMT

Yes, I remember I faced the same in the past in an intersection scenario.