Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
Antoine04
Partner - Contributor II
Partner - Contributor II

Intersection of alternate states does not work as expected

Hi guys,

The subject is actually very simple :

1. I want to do an intersection with 2 alternate states : I would like to get the number of customers who ordered something in 2021 AND in 2022

2. For this, I created 2 alternate states : S1 and S2 : I put the selections "Year=2021" on S1 and "Year=2022" on S2

3. When I am calculating the number of customers separately, it works. As you can see on the table below (columns 2 and 3 of the table : 177 in each year)

4. But when I am doing the intersection between the both S1 and S2, it does not work ....

5. Nevertheless, when I am counting on another field (Customer : which is in another table), it works !

Any idea about this issue ? It looks like Qlik does not like the intersection on a keyfield or something like this ...

Attached you will find the app, the data model and the table 🙂

Regards,

Antoine Lepoutre

Labels (3)
1 Solution

Accepted Solutions
rubenmarin

I did a testing that confirms your thoughs. With this data:

TableA:
LOAD * INLINE [
year,customer
2020,A
2021,A
2020,B
];

Selecting 2020 in S1 and 2021 in S2.

=count({S1*S2} DISTINCT customer) -> Returns 0.

If I select 2020 in S1 and S2 returns 2, with 2021 returns 1

Adding this table:

TableB:
LOAD * Inline [
customer, other
A, a
B, b
];

Selecting 2020 in S1 and 2021 in S2.

=count({S1*S2} DISTINCT customer) -> Returns 0

=count({S1*S2} DISTINCT other) -> returns 1

For some reason the table where the field filtered is (year in this case) doesn't have any row in the intersection, may have some reason because there is no row with year 2020 and 2021, so it returns an empty dataset. But going to another table it returns data, meaning that the dataset was not empty.

BTW, =Count({S1<customer=P({S2} customer)>} distinct customer) works with only TableA.

View solution in original post

7 Replies
rubenmarin

Hi, I can't explain this, it should be something with using key fields, also is hard to check when all year combinations returns 177.

This expression seems to work: Count({S1<id_client=P({S2} id_client)>} distinct id_client)

But as it always returns 177 I can't be sure if  it's really applying year filters as expected.

menta
Partner - Creator II
Partner - Creator II

I suggest to create a new field fo id_client and don't use the key in count expression

Antoine04
Partner - Contributor II
Partner - Contributor II
Author

Thanks for answering guys 😉

Indeed, the Dataset isn't the best, I agree.

I also thought first that was due to the key field but I did another try by having only one table just like this : 

 Antoine04_0-1663598692671.png 

And... it still does not work : 

Antoine04_1-1663598735154.png

So, my theory is just that it should not be a field in the main table, because then the intersection is empty ...
Maybe better then to create another id_customer in the customer table in my case.

menta
Partner - Creator II
Partner - Creator II

maybe

Count({<S2>*<S1>} distinct id_client)

rubenmarin

I did a testing that confirms your thoughs. With this data:

TableA:
LOAD * INLINE [
year,customer
2020,A
2021,A
2020,B
];

Selecting 2020 in S1 and 2021 in S2.

=count({S1*S2} DISTINCT customer) -> Returns 0.

If I select 2020 in S1 and S2 returns 2, with 2021 returns 1

Adding this table:

TableB:
LOAD * Inline [
customer, other
A, a
B, b
];

Selecting 2020 in S1 and 2021 in S2.

=count({S1*S2} DISTINCT customer) -> Returns 0

=count({S1*S2} DISTINCT other) -> returns 1

For some reason the table where the field filtered is (year in this case) doesn't have any row in the intersection, may have some reason because there is no row with year 2020 and 2021, so it returns an empty dataset. But going to another table it returns data, meaning that the dataset was not empty.

BTW, =Count({S1<customer=P({S2} customer)>} distinct customer) works with only TableA.

Antoine04
Partner - Contributor II
Partner - Contributor II
Author

Already tried and it does not work. As you can see with this example :

Antoine04_0-1663600407764.png

The really weird thing is that it works in the table.... but not in the KPI.

Antoine04
Partner - Contributor II
Partner - Contributor II
Author

Thanks Ruben,

I can now sleep on my 2 ears. 😅

Regards,