Count with conditions

[timol]

Dear all

I have the following data structure

ID	Created	Experience
1	2017	2015
1	2017	2016
1	2017	2017
1	2017	2018
1	2017	2019
1	2017	2020
2	2015	2015
2	2015	2016
2	2015	2017
2	2015	2018
2	2015	2019
2	2015	2020

Now I would like to count the experience column but only if the year if <= Created

see an example output

ID	Year	Count
1	2015	0
1	2016	0
1	2017	3
1	2018	4
1	2019	5
1	2020	6
2	2015	1
2	2016	2
2	2017	3
2	2018	4
2	2019	5
2	2020	6

Is it right to use the Summarize tool with two group by options?

[AngelosPachis]

Hi @timol ,

You can try counting the years first for each ID and then with a formula tool, you can set those that don't meet your condition to 0

Hope that helps, let me know if that works for you.

Cheers,

Angelos

[AbhilashR]

Hi @timol, in addition to the multi-row formula approach suggested by AngelosPachis, you could use the TileTool or RunningTotal tool to compute the count and then use the formula tool to reflect the logic of Created vs Experience. The Summarize tool might not work in your case given you are looking to do some form of running total (or a rowID within a given ID).

[timol]

Thanks to both of you!

It works

But I think right now it only works if I have one value for each year right?

I want to print out a value for every year in a given time span e.g. 2015-2020

[AngelosPachis]

@timol  if you 've found that you are missing some years and you want to generate all years between a certain range for each ID, you can use a generate rows tool to create that list of years for each ID.

Then it's pretty much the same process.

Hope that helps, let me know if that worked for you.

Cheers,

Angelos