How to group a file by dates and check if record exists in both groups

[MysticalAz]

Hi All!

This is my first time in the Alteryx community and I hope to learn a lot from everyone!

I have the attached sample data where it includes three columns: Vendor ID, Date and Transaction Amount.

Vendor ID	Date	Transaction Amt
1	1/1/2021	500
1	2/2/2021	100
2	3/1/2021	200
2	4/1/2021	600
3	6/1/2021	1000
3	6/2/2021	2000
4	7/1/2021	3000
4	8/1/2021	400000
1	8/5/2021	5500
1	8/15/2021	500

I would like to put Vendor IDs in two groups, based on date and then see if any Vendor ID populates for both Group 1 and Group 2. If it does, I only want to return those records that are in both groups. The output would just display Vendor ID and Sum of transactions between 7/1 - 9/1. 

Group 1 = Date between 1/1/2021 - 6/30/2021

Group 2 = Date between 7/1/2021 - 9/1/2021

In this case, only Vendor ID 1 populates in both date ranges. Expected output attached. 

Vendor ID	Sum Transaction Amt
1	6000

What I've done so far is use a formula that would split the Dates into Groups with: 

[Date]>= "2021-01-01" and [Date] <= "2021-06-30" then "Group 1"
else "Group 2"
endif

Then I used the Summarize tool to group by Vendor ID and Group. I think created another formula in attempts to find those in both groups but with no luck. 

Any help would be appreciated!

[drew9]

Hi @MysticalAz ,

The formula you have is a great start. From there, we can group by Vendor ID and do a distinct count of the Group field we created. If a vendor appears in multiple groups, it will be greater than one. Filter for CountDistinct_group is greater than one, and then join that result back to our original dataset. from there, we simply filter for the date range of 7/1-9/1 and do a sum of Transaction amount by vendor ID. Hope this helps!

[MysticalAz]

Hello and thank you! Can you upload the workflow so I can follow better?

[drew9]

Sure!

[MysticalAz]

Thank you! 

Also, I'm sorry I messed up on the description I needed. If I wanted to  populate only those vendors that are in Group 2 but not in Group 1, would Count > 1 still be relevant?

For example:

Vendor ID	Date	Transaction Amt	Group
1	1/1/2021	500	Group 1
1	2/2/2021	100	Group 1
2	3/1/2021	200	Group 1
2	4/1/2021	600	Group 1
3	6/1/2021	1000	Group 1
3	6/2/2021	2000	Group 1
7	7/1/2021	3000	Group 2
7	8/5/2021	400000	Group 2

Vendor ID 7 was in Group 2 but not in Group 1. 

Vendor ID	Transaction Amt
7	403000

[drew9 · Accepted solution]

Similar process as last time, this time we are looking for where distinct count of group equals 1, and where the concatenation of group contains group 2. Attached an example below 🙂 

[MysticalAz]

You have been super helpful!! Thank you very much for your prompt response.