How To Find Set Difference Between Distinct Set Values?

[PragyanSubedi]

Hello,

I have two dimensions: 'Month' and 'Name'.

I want to find the count of unique elements that exist in 1/1/2018 and not in 1/2/2018 as shown in the photo:

I'm trying to do something like this:

Count(({<Month={'1/1/2018'}>} Distinct [Name]) - ({<Month={'1/2/2018'}>} Distinct [Name]))

But the above syntax sadly doesn't work.

To explain my intention, I am trying to:

- First, find the distinct elements in each date to create set A and set B.

A = {'Apple', 'Orange}, B = {'Apple', 'Banana'}

- Then, find the difference between sets A and B (elements that lie in set A and not in set B).

A - B = {'Orange'}

- Finally, find the count of the difference between sets A and B (count of elements that lie in set A and not in set B).

Count(A - B) = 1

How can I do this?

[BrunPierre]

Please correct me if I'm wrong, but shouldn't "Banana" be the only unique fruit between the two dates?

[PragyanSubedi]

No. @BrunPierre 

To clarify, if you consider the distinct values for date '1/1/2018' as set A and distinct values for date '1/2/2018' as set B, then A - B = {'Orange'} (their set difference).

So, the count of A - B would be 1.

[BrunPierre]

In that case, off the top of my head, I'd do something like this

Set A : Count(DISTINCT TOTAL IF(Month='1/1/2018',Name))

Set B: Count(DISTINCT TOTAL IF(Month='1/1/2018',Name))

Expected output: 

Count(DISTINCT TOTAL IF(Month='1/1/2018',Name)) - Count(DISTINCT TOTAL IF(Month='121/2018',Name))

[JordyWegman · Accepted solution]

Hi Pragyan,

You're looking for this calculation:

Count(Distinct {$< Month = {'1/1/2018'}, Name = e( {< Month = {'1/2/2018'} >} ) >} Name)

It's a nice way of using E().

Example data that I've used:

Load * Inline [
Month, Name
1/1/2018, Apple
1/1/2018, Apple
1/1/2018, Orange
1/1/2018, Strawberry
1/2/2018, Apple
1/2/2018, Banana
];

Jordy
Climber
 

[PragyanSubedi]

@BrunPierre  The issue with the recommended solution would be that you are first calculating the count of distinct values for each date and then subtracting the counts which results in 0 since both dates have an equal number of distinct elements.

I'm trying to do something like this:

Count(({<Month={'1/1/2018'}>} Distinct [Name]) - ({<Month={'1/2/2018'}>} Distinct [Name]))

But the above syntax sadly doesn't work.

To explain my intention, I am trying to:
- First, find the distinct elements in each date to create set A and set B.

A = {'Apple', 'Orange}, B = {'Apple', 'Banana'}

- Then, find the difference between sets A and B (elements that lie in set A and not in set B).

A - B = {'Orange'}

- Finally, find the count of the difference between sets A and B (count of elements that lie in set A and not in set B).

Count(A - B) = 1

[PragyanSubedi]

Thank you! This works @JordyWegman