topic Re: Exclusion does not work (set expressions) in App Development

Exclusion does not work (set expressions)

RoyBatty — Wed, 28 Feb 2024 22:25:18 GMT

Hi,

We have two tables that have only one dimension (user_id) and nothing else:

Table "S Users":

Table "E Users":

For "S Users" we use the following set expression:

=Aggr(
    Only({<[Standard Activity Period]={'N'}, [Activity]={'Payment', 'Support'}>} distinct [user_id]), 
    [user_id]
)

For "E Users" we use the following set expression (the only difference is 'Y' instead of 'N'):

=Aggr(
    Only({<[Standard Activity Period]={'Y'}, [Activity]={'Payment', 'Support'}>} distinct [user_id]), 
    [user_id]
)

We want to create a third table that will show only those "S Users" that are not in "E Users" (S - E). We tried the following with the Exclusion (-) operator:

=Aggr(
    Only(
        {
           $<[Standard Activity Period]={'N'}, [Activity]={'Payment', 'Support'}>
           -
           $<[Standard Activity Period]={'Y'}, [Activity]={'Payment', 'Support'}>
        } 
        distinct [user_id]
    ), 
    [user_id]
)

but it doesn't work, it gives a wrong result (please see the third table in the image below):

The expression is probably written incorrectly (there is no error but it does not give the correct result), but we cannot find out what the problem is and how we can solve this.

Do you have any ideas or suggestions as to what that expression should look like? Thanks in advance!

Re: Exclusion does not work (set expressions)

Zapparoli — Wed, 28 Feb 2024 23:59:34 GMT

I'm not really sure why you did those expression, let me know if i'm missing something, but this is what I would do:

If your table has the, User ID, Standard Activity Period and the Activity fields, you could do:

aggr( Only( {<ActivityPeriod={'Y'} >} user_id) , user_id)

Basically, you are getting only the fields that contains the "Y" on the Standard Activity Period Field, then I use Aggr by user_id, and return only the field that I want:

Let me know if this helps you.

-Zapparoli

Re: Exclusion does not work (set expressions)

ali_hijazi — Thu, 29 Feb 2024 07:48:35 GMT

sometimes we use this approach when there is no expression to be computed
so as he's saying above, we wants to list the dimension values satisfying some conditions

Re: Exclusion does not work (set expressions)

ali_hijazi — Thu, 29 Feb 2024 07:56:11 GMT

Let me explain to you:

I tried to mimic your situation
in the first table I'm dispaying users where type = Y and profession ={IT,support}
in the second table I'm displaying users where type = N and profession {IT,support}

now with your approach ali, and jean will appear
so you want to exclude users who only have type = Y
so your expression would be :
aggr(Only{<user={"=type='Y'"}>}user),user)

and this is what is displayed in the last table to the right

the data that I used is as follows:
load * Inline [
user, type, profession
ali, Y, support
ali, N, IT
jean, Y, IT
maher, N, support
amir, Y, home
omar, N, IT
];

the expressions used in the tables are:
1.=aggr(only({<type={Y},profession={[support],[it]} >}user),user)
2.=aggr(only({<type={N},profession={[support],[it]} >}user),user)
3.=aggr(only({
<type={Y},profession={[support],[it]} >
-
<type={N},profession={[support],[it]} >
}user),user)

4. =aggr(only({
<user={"=type='Y'"},profession={[support],[it]} >

}user),user)

hope this helps

Re: Exclusion does not work (set expressions)

RoyBatty — Thu, 29 Feb 2024 14:25:10 GMT

Thank you so much!