Intersection of alternate states does not work as expected

[Antoine04]

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

[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]

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

[Antoine04]

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 : 

  

And... it still does not work : 

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]

maybe

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

[rubenmarin · Accepted solution]

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]

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

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

[Antoine04]

Thanks Ruben,

I can now sleep on my 2 ears. 😅

Regards,