topic Re: Intersection of alternate states does not work as expected in App Development

Intersection of alternate states does not work as expected

Antoine04 — Mon, 19 Sep 2022 13:05:06 GMT

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

Re: Intersection of alternate states does not work as expected

rubenmarin — Mon, 19 Sep 2022 14:30:54 GMT

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.

Re: Intersection of alternate states does not work as expected

menta — Mon, 19 Sep 2022 14:36:43 GMT

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

Re: Intersection of alternate states does not work as expected

Antoine04 — Mon, 19 Sep 2022 14:47:40 GMT

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.

Re: Intersection of alternate states does not work as expected

menta — Mon, 19 Sep 2022 15:03:49 GMT

maybe

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

Re: Intersection of alternate states does not work as expected

rubenmarin — Mon, 19 Sep 2022 15:10:22 GMT

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.

Re: Intersection of alternate states does not work as expected

Antoine04 — Mon, 19 Sep 2022 15:14:06 GMT

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.

Re: Intersection of alternate states does not work as expected

Antoine04 — Mon, 19 Sep 2022 15:17:16 GMT

Thanks Ruben,

I can now sleep on my 2 ears. 😅

Regards,