Let’s talk Alteryx Copilot. Join the live AMA event to connect with the Alteryx team, ask questions, and hear how others are exploring what Copilot can do. Have Copilot questions? Ask here!
Start Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

COUNTIFS - Dynamic conditions

drh
6 - Meteoroid

Hi all,

 

I am currently looking for a more complex "countifs" formula.

I would like to identify in each row if the combination IDa_IDb can be found in the past five years. As an example (please see my table below):

  • In row 1, we find the combination: IDa = 20, IDb = 3, in 2016. As we do not know anything about previous years, the count should be 0.
  • In row 3, we find the combination: IDa = 20, IDb = 3, again. As we also find it in 2016 (row 1), the count should be 1.

Unfortunately, I have a dataset of more than 10 million rows and I do not know how to create such "count" column. Does anyone have an idea how to solve it? I would really appreciate it!

 

Thanks a lot!

 

Best,

Alexander

 

IDIDaYearIDbCount
120201630
120201640
220201731
320201831
320201841
320201850
420201951
522201630
522201640
10 REPLIES 10
drh
6 - Meteoroid

Hi Dan, thanks a lot for your help! That works just fine!

@apathetichell, thanks also for supporting me!

Labels
Top Solution Authors