(More) CountIfs type questions

Question

I've been through various discussions on recreating something akin to the the Excel CountIfs function, but have been struggling for 2 days. Basically, I need have a row be able to count any previous rows (it will be previous by default) that match certain criteria So, for a given row, as long as its type <> "Online", I want to count records/rows where Cust ID = Cust ID, Type DOES = Online in a previous record, and the Purch date of the row to be counted is between the purchase date the row in question and 30 days prior. In the data below, the later occurrences of CustIDs 199003 and 135833 (I tried to bold type) would each show a count of 1 (assumedly in a new column so i can flag it later), as they each are <>Online, but there is row with a matching CustID, where Type = Online, and was within the previous 30 days. Purch Date APPPurch Date-1Purch Date less 30Purchase#TypeCustID6/30/20216/29/20215/30/20212260787Online1990037/1/20216/30/20216/1/20212153512Online1358337/21/20217/20/20216/21/20212575005Shop21466517/21/20217/20/20216/21/20212871696Shop11904527/21/20217/20/20216/21/20212834699Shop11832257/21/20217/20/20216/21/20212934265Shop11516817/21/20217/20/20216/21/20212807218Shop21137387/21/20217/20/20216/21/20212967201Shop21270267/21/20217/20/20216/21/20212487600Shop21990037/21/20217/20/20216/21/20212693672Shop21605857/21/20217/20/20216/21/20212058022Online1778087/21/20217/20/20216/21/20212866540Online1113397/21/20217/20/20216/21/20212745257Shop11358337/21/20217/20/20216/21/20212602772Shop11168417/21/20217/20/20216/21/20212899620Shop1155846 AlteryxCountIfs example.xlsx

JKirstine · Answer

Thanks to everyone who provided a solution. I will look at the multi-row formula.

I was able to use a Filter + a Join to get the records I was eventually after, even though I didn't get the flag I was looking for as part of the original solution i had in mind (really need to stop thinking in Excel....LOL). I did make one change from the request in that I used a 14 day window via a new variable rather than the 30 day window I originally talked about. But, it does work and gives me what I need. This is just the proof of concept workflow, but in applying it to my larger dataset and regression testing, it was dead on.

Phillip - I will take a look at your solution as I have never attempted Alteryx macros before. This might be a good lesson for me.

Have to say, I love this tool!

MPCA as Lead POC.yxmd

mceleavey · Answer

@JKirstine ,

I did this with a simple multi-row formula.

Hope this helps,

M.

mceleavey solution.yxmd

FlorianC · Answer

Hi @JKirstine,

In order to replicate the COUNTIF formula from Excel, I would recommend using the multi-row formula tool. You can easily group by customer ID and look at the values from the previous rows to determine whether the purchase was made online and within 30 days.

Please find attached an example workflow demonstrating how to to this.

Example workflowWorkflow results

Workflow.yxzp