Count with conditions

Question

Dear all

I have the following data structure

IDCreatedExperience120172015120172016120172017120172018120172019120172020220152015220152016220152017220152018220152019220152020

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

see an example output

IDYearCount120150120160120173120184120195120206220151220162220173220184220195220206

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

AngelosPachis · Answer

@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

example_v2.yxmd

timol · Answer

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

AbhilashR · Answer

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).

sample.yxmd