Data Island Exclusion set analysis

[gmenoutis]

I have a customers table, with [Customer Code] being the linking field, and a transactions table which among others includes [Transaction Store Code] and [Customer Code].

I also have made a data island which includes the field [NOT Transaction Store Code]. The point is to have a table with the customer list such that when some [NOT Transaction Store Code]s are selected, the [Customer Code]s which have some transactions on these stores are excluded.

I have thus created the following calculated dimension:

aggr(only({<[Transaction Store Code]-=[NOT Transaction Store Code]>} [Customer Code]),[Customer Code])

and set it to not show nulls.

This thing indeed does what I want. Unfortunately, it also hides [Cutomer Code]s which have no transactions at all. Is there any way to get around this?

[sunny_talwar]

You mean those rows where Transaction Store Code is null?

[gmenoutis]

I am not very sure how to answer this. There are no rows in the transactions table where [Transaction Store Code] is null; BUT, there are rows in the customer table for which there are no transactions with such a [Customer Code].

[sunny_talwar]

That makes sense, so essentially the data is missing for certain Customer Code. Not sure if this will work or not, but can you try this

Aggr(Only({<[Transaction Store Code] = e([NOT Transaction Store Code])>} [Customer Code]), [Customer Code])

[gmenoutis]

It doesn't work, same problem

[sunny_talwar]

Would you be able to share a sample to try out few things?

[gmenoutis]

I am creating a simple warehouse with sample data for this purpose. After I test a few alternatives I'll post it here.

[sunny_talwar]

Sounds good.

[gmenoutis]

Here are the specs:

When no [NOT Transaction Store Code]s are selected, the target calculated dimension should return all [Customer Code]s: C1,C2,C3

When [NOT Transaction Store Code] has S1 selected, this leads to [TranID]s T1,T2,T3, which in turn lead to [Customer Code]s C1,C2. These must be excluded, so I should only get C3. This case is also identical to selecting both S1,S2 as the [Customer Code]s implied are the same.

When [NOT Transaction Store Code] has S2 selected, this leads to [TranID] T4, which in turn leads to [Customer Code] C2. This must be excluded, so I should only get C1,C3.

...and here is the code:

customers:

Load * inline

[

Customer Code

C1

C2

C3

];

transactions:

Load * inline

[

TranID,Transaction Store Code,Customer Code,Price

T1,S1,C1,50

T2,S1,C1,40

T3,S1,C2,30

T4,S2,C2,60

];

negateStore:

Load * inline

[

NOT Transaction Store Code

S1

S2

];

[sunny_talwar]

Try this

=Aggr(Only({<[Transaction Store Code] = e([NOT Transaction Store Code])>/<Flag = {1}>} [Customer Code]), [Customer Code])

Where Flag is created in the customer table like this

customers:

LOAD [Customer Code],

1 as Flag;

LOAD * INLINE [

    Customer Code

    C1

    C2

    C3

];

transactions:

LOAD * INLINE [

    TranID, Transaction Store Code, Customer Code, Price

    T1, S1, C1, 50

    T2, S1, C1, 40

    T3, S1, C2, 30

    T4, S2, C2, 60

];

negateStore:

LOAD * INLINE [

    NOT Transaction Store Code

    S1

    S2

];