topic Re: Set Analysis Operators not calculating result correctly in App Development

Set Analysis Operators not calculating result correctly

JohannEls — Tue, 30 Nov 2021 16:25:32 GMT

Hi,

I would like to understand why the operators in the following KPI objects in Qlik Sense is not calculating the results correctly. See image below.

On the left you will find the data set and on the right you will find three KPI objects. I have added the expression of each one of the KPI objects as a foot note and have also added the correct answer that I am expecting as a heading at the top of each KPI object.

I am running it on Qlik Sense Desktop November 2021 (14.44.5)

I am out of ideas - some of my students could get the correct results on older versions of Qlik Sense.

Any suggestions would be welcome!

Thanks

Re: Set Analysis Operators not calculating result correctly

tresB — Thu, 25 Nov 2021 08:47:51 GMT

Could you share your qvf?

Re: Set Analysis Operators not calculating result correctly

JohannEls — Thu, 25 Nov 2021 09:42:13 GMT

Hi,
No problem - please find app attached below.

Incorrect App has been deleted - find correct App download in later chat below.

Re: Set Analysis Operators not calculating result correctly

tresB — Thu, 25 Nov 2021 09:13:17 GMT

I see in my desktop (Nov 2020) as:

Re: Set Analysis Operators not calculating result correctly

JohannEls — Thu, 25 Nov 2021 09:25:23 GMT

I see that your fourth KPI from the top is also still calculating it incorrectly. Is there perhaps a setting on Qlik Sense desktop that you might think that could cause this?
In my class on Set Analysis - some students get the same incorrect results as I do and some get the correct results and some partially correct results.
Here is the screen pic on my side again:

Re: Set Analysis Operators not calculating result correctly

tresB — Thu, 25 Nov 2021 09:26:42 GMT

Your third KPI expression is wrong; it is not the same as being shown on the footer.

Re: Set Analysis Operators not calculating result correctly

JohannEls — Thu, 25 Nov 2021 09:30:01 GMT

Sorry - saw that just now - still one of the tests that I have done - just change the last KPI's Expression to what is in the footer. I will do the same on my side and share the screen pic.

Re: Set Analysis Operators not calculating result correctly

JohannEls — Thu, 25 Nov 2021 09:32:32 GMT

Re: Set Analysis Operators not calculating result correctly

JohannEls — Thu, 25 Nov 2021 09:37:11 GMT

Here is the app again with the correction done on the bottom KPI. But it is still giving me the same incorrect results.

Re: Set Analysis Operators not calculating result correctly

tresB — Thu, 25 Nov 2021 09:39:46 GMT

I can't test on Nov 2021 version now. What about the versions and results your class mates are using?

Re: Set Analysis Operators not calculating result correctly

JohannEls — Thu, 25 Nov 2021 09:44:46 GMT

Unfortunately I do not have any connection to the students anymore. The class was held yesterday.

Re: Set Analysis Operators not calculating result correctly

JohannEls — Thu, 25 Nov 2021 09:51:58 GMT

I have also experienced this with a prior version earlier this year during the same course that I gave.
I have absolutely no idea what causes this inconsistency with Set Analysis in Qlik Sense.
Perhaps Qlik could shine some light on this issue...

Re: Set Analysis Operators not calculating result correctly

Or — Thu, 25 Nov 2021 10:11:59 GMT

I'm not sure what's going on behind the scenes with the Qlik engine, but I did notice that I get the incorrect values like you did using your formula, but if I replace EmployeeID with EmployeeName, I get the correct result. Perhaps there's an issue here related to aggregating a key field when applying set operators across multiple tables - I was able to generate similar problems using even the most basic set operators, for example the set here should return a list of all employee IDs but it only returns 1 through 9.

Presumably not coincidentally, these are all of the employees which have sales, so my guess is that the engine is counting out the EmployeeID from the OrderHeader or Territories table rather than the one in the Employees table. Perhaps an odd aspect of a multi-table key field with set operators (though this doesn't seem like it should be anything intentional to me)?

Re: Set Analysis Operators not calculating result correctly

JohannEls — Thu, 25 Nov 2021 10:24:55 GMT

I have now changed the count to rather count the EmployeeName as mentioned by "Or" and it calculated it perfectly.

Here is my latest result:

Here is a peek at the Data Model:

Re: Set Analysis Operators not calculating result correctly

tresB — Thu, 25 Nov 2021 10:45:49 GMT

@Or good catch!

I have seen this behavior of key field analysis issue in set analysis earlier also. However, never thought that it could be an issue with versioning. The exact same application gave different results in different versions. @Or , could you please let us know the version of your qs?

Re: Set Analysis Operators not calculating result correctly

Or — Thu, 25 Nov 2021 10:57:35 GMT

@tresB I tested this with May 2021 Enterprise. I've never tried counting a key field before while also applying set operators (I don't typically need unions / intersects / etc), and I don't really understand why this would happen, but I can't think of any other explanation here. I'm not sure who at Qlik would be the right person to ask, either, or I'd tag them here.

@JohannEls Please do keep in mind that counting employee names is not a safe approach, since two different employees might have the same name. Instead, I would suggest that you modify the dataset by adding a second, differently-named employee ID to the Employee table:

Load EmployeeID,

EmployeeID as EmployeeIDForCount,

[...]

From Employees;

You would then have an unambiguous field to count that isn't a key to any other table, and hopefully that should give you consistent and correct results.

Re: Set Analysis Operators not calculating result correctly

JohannEls — Thu, 25 Nov 2021 11:07:37 GMT

100% - I agree!