COUNTIFS
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Dear all
I have the following data structure
Company | Year1 | Year2 |
A | 2018 | 2015 |
A | 2018 | 2015 |
A | 2018 | 2016 |
A | 2018 | 2016 |
A | 2018 | 2016 |
A | 2018 | 2018 |
A | 2018 | 2018 |
A | 2018 | 2018 |
A | 2018 | 2018 |
Now I want Alteryx to give out a count for each year 2015-2020
But it should only count of Year 2 os lower of equal to Year1
So that would be the output
Company | Year | Count |
A | 2015 | 0 |
A | 2016 | 0 |
A | 2017 | 0 |
A | 2018 | 9 |
Can anyone help me doing this in Alteryx?
Solved! Go to Solution.
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you so much for all your help!!!!
But isn't it the case that the condition is true for the first 11 rows?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello @timol ,
The condition is not true for the first 11 rows. Row 10 and 11 have both year1 = 2017 and year2 2017 and 2018.
Since you stipulated that year2 has to be equal or lower it cannot be the case for row 11.
Company | Year1 | Year2 | Founding |
A | 2018 | 2015 | 2013 |
A | 2018 | 2015 | 2013 |
A | 2018 | 2016 | 2013 |
A | 2018 | 2016 | 2013 |
A | 2018 | 2016 | 2013 |
A | 2018 | 2018 | 2013 |
A | 2018 | 2018 | 2013 |
A | 2018 | 2018 | 2013 |
A | 2018 | 2018 | 2013 |
A | 2017 | 2017 | 2013 |
A | 2017 | 2018 | 2013 |
A | 2019 | 2015 | 2013 |
A | 2019 | 2017 | 2013 |
A | 2019 | 2019 | 2013 |
A | 2019 | 2012 | 2013 |
A | 2019 | 2011 | 2013 |
A | 2019 | 2007 | 2013 |
B | 2016 | 2011 | 2015 |
B | 2016 | 2011 | 2015 |
B | 2016 | 2015 | 2015 |
B | 2016 | 2015 | 2015 |
Regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You are absolutely right
Sorry for the confusion!
The output I would like to generate looks like this:
Company | Year | Count |
A | 2013 | 0 |
A | 2014 | 0 |
A | 2015 | 0 |
A | 2016 | 0 |
A | 2017 | 1 |
A | 2018 | 11 |
B | 2015 | 0 |
B | 2016 | 4 |
B | 2017 | 4 |
B | 2018 | 4 |
I created the following excel sheet:
In Cell H2 you can see the formula I used:
=+COUNTIFS($A$1:$A$22;F2;$B$1:$B$22;"<="&G2;$C$1:$C$22;"<="&G2)
Again the minimum Year should be the Founding year and the maximum year should be 2020.
That is what I actually want to rebuild in Alteryx. Is there any way to do this?
Thank you so much for your help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- « Previous
-
- 1
- 2
- Next »