intersection without using P()

[April1234]

Hi , I would like to know if it is possible to have intersection without using P()  .

I used the below calculations and does not give me correct results.

1) Count({<ID=p({<year={'2022'}>})>*p({<year={'2023'}>})>}distinct ID) 

this calculation bumps up numbers since the individual P() also does that.

2) Count({<year={'2022'},ID=P({<year={'2023'}>}ID)>}distinct ID) 

This calculation from other post is working to some extent but here and there bumps up the numbers a little and I saw P() adds up extra ID's in the count .

So I have Count({<year={'2022'}>}distinct ID) and Count({<year={'2023'}>}distinct ID) , which fetch me correct results , is it possible to have a intersection between them?

Thank you!!

[hic]

The top expression has one angle bracket '>' too much. Try the following

Count(

{

<ID= p({<year={'2022'}>}) * p({<year={'2023'}>})>

}

distinct ID

) 

[April1234]

I tried it but does give me same results as the above mentioned query. Thank you

[marcus_sommer]

p() and the opposite e() are returning the possible respectively excluded values of a field and are not directly related to an intersection of sub-sets else this is done with the operators between the set-statements.

Before going further I suggest you take a table-box with the relevant fields - here at least year and ID - and then selecting within the table-box and/or with list-boxes the wanted values. If you are not able to get the intended results with the selections you wouldn't get them with any set analysis.

I could imagine that you want to count ID's which have any data within 2022 AND 2023 and this probably in regard to any other dimensionality. This kind of query is often not possible without any if-loops and/or any multi-level evaluations/aggregations. Depending on the real scenario this could become complex but sometimes it's quite easy - therefore you may try something like this:

count({< year = {2022,2023}>} ID) * -(count({< year = {2022,2023}>} distinct year)=2)

which means the count of ID's is combined with a boolean check if there two years exists.

[BawejaMedia]

Yes, it's possible to find the intersection of two sets without using the P() function in Qlik Sense or QlikView. You can achieve this by using set analysis expressions to filter data based on conditions.

Here's how you can find the intersection of two sets (IDs from the year 2022 and IDs from the year 2023) without using the P() function:

Count({<year={'2022'}, ID={"=Count({<year={'2023'}>} ID)"}>} DISTINCT ID)

In this expression:

• {<year={'2022'}, ID={...}>} filters the data to include only records from the year 2022.
• Count({<year={'2023'}>} ID) calculates the count of distinct IDs for the year 2023.
• ID={"=Count({<year={'2023'}>} ID)"} filters the data further to include only IDs that exist in both years. The expression inside the double quotes evaluates to the count of distinct IDs for the year 2023, effectively creating an intersection.

This approach ensures that only IDs present in both years are counted, without the need for the P() function. Also, check out baweja Media.