Alteryx and Excel style countifs/sumifs with dates criteria

[aleksi555]

Hey, I have a simple problem as I want to use countifs and sumifs functions in Alteryx. I have two datasets and I need to find out how many times a certain corporation ID appears in other dataset in a given time period.

Dataset1 is like this:

Column1=CorporationID, Column2= date1, Column3=date2.

Dataset2 is like this:

Column1=CorporationID,Column2=date3,Column3=price. 

Now I first want to know how many times corporationID in dataset1 appears in dataset2 based on a condition that date3 falls between date1 and date2.

Second, I want to know what is the sum of "price" based on corporationID and certain daterange. I attached an excel and I would like to reproduce those results in Alteryx. 

Excel has both example datasets.

[jdunkerley79]

Probably the easiest way is:

- Use a Join Tools to join the two dataset (join on Corporation ID)

- Use a filter tool to pick the rows where date3 is between date1 and date2

- Use a summarise tool to create the Count and Sum

- Finally you need to get back the rows which have been lost so I would join back to the original DataSet1 and then union the unjoined rows.

Sample Attached

[aleksi555]

Hey, thanks for your help. I forgot to add that in my real data I have corporationid:s many times in both datasets so im not sure can i use "join" then?

Best regards.Aleksi

[aleksi555]

Hey, I attached renewed dataset where there are few same corporationsID:s in both datasets. 

[jdunkerley79 · Accepted solution]

Fairly simple adjustment including the Date1 and Date2 in the Summarise Group and in the second Join.

Attached updated sample.

[aleksi555]

Hey, thanks that solves the problem!