Complex Excel countifs function NOT POSSIBLE in Alteryx

[NYJ1]

I have a list of SEC Form 4 filing insider purchases in this attached excel list. I.e. different CEOs, CFOs, Directors buying shares of their own companies, the price they bought at, the date they bought at etc. 

What I did in Excel was to calculate how many different transactions/ purchases were there of the same stock in the last 20-days (number of days configurable), as shown in the yellow column below.

However, i cannot find a clean way in Alteryx to do this. Can anyone advise how to create this in Alteryx? 

[jasperlch]

Hi @NYJ1 

It is possible. This can be achieved by

1, Self joining the dataset on Company

2. Formula to calculate the date difference between the left and right input

3. Filter to keep only records with date difference within 0 and -20

4. Summarise to count the cases for each record from the left

5. Join the count results back to original dataset on S/N

Hope this helps. 

Jasper

[NYJ1 · Accepted solution]

No problem; i got it! thank you