SUM/COUNT values for every year in a time span

[timol]

Dear all,

I have the following data structure

A	1	12.01.2020
A	2	12.01.2020
A	23	12.01.2020
A	21	12.01.2019
A	21	12.01.2018
A	21	12.01.2015
A	21	12.01.2015
B	23	12.01.2019
B	42	12.01.2020
B	23	12.01.2019
C	1	12.01.2019

Now I would like to just sum the values in the second column for every year 2015-2020 to get this output

I would like to accumulate values in every year.

But (for whatever reason) one company has only values for 2015, 2018, 2019 and 2020.

I still want to have values for 2017-2019.

A	2015	42
A	2016	42
A	2017	42
A	2018	63
A	2019	84
A	2020	110

Does anyone have an idea how to do that in Alteryx?

Thank you so much!

[afv2688]

Hello @timol ,

does this work for you?

Regards

[timol]

Thank you!!

But what I would like to do is list a value for every year in a timespan (so also 2016 and 2017, see the table below)

A	2015	42
A	2016	42
A	2017	42
A	2018	63
A	2019	84
A	2020	110

[afv2688]

Hello @timol ,

sorry for that, interpreted what I wanted not what was written 😥

Regards

[timol]

Thanks again for the super fast response

But what I would like to do is place the accumulated value of all previous years in the fied

currently is says for the years that are not explicitly mentioned "Null"

[afv2688 · Accepted solution]

Hello @timol ,

Could you please explain what is wrong with this solution?

As far as I can see the acumulated results are there.

Regards

[timol]

you are right it works

But is this solution also flexible? Assume I have 100 companies. My general timespan is 2015 to 2020. But some companies were founded after 2015. So for these companies the values should be either Null or for these companies the timespan should start in the year of their founding.

See a simplified example below

	Founded	Certificates	Date
A	2015	1	12.01.2020
A	2015	2	12.01.2020
A	2015	23	12.01.2020
A	2015	21	12.01.2019
A	2015	21	12.01.2018
A	2015	21	12.01.2015
A	2015	21	12.01.2015
B	2019	23	12.01.2019
B	2019	42	12.01.2020
B	2019	23	12.01.2019
C	2016	1	12.01.2020
C	2016	2	12.01.2020
C	2016	23	12.01.2020
C	2016	21	12.01.2019
C	2016	21	12.01.2018
C	2016	21	13.01.2018
C	2016	21	14.01.2018

[afv2688]

Hello @timol ,

It does work dinamically, it depends on the min year and max year for each company. They are not fixed.

Regards

[timol]

Thank you

But I would also like to cover the following case

Assume the min year is 2016 and the max is 2020

The company was founded in 2015

I still want to give the company in 2015 a value of 0, since I would like to cover all years from the founding year onwards

[afv2688]

Hello @timol ,

You can add a table where you manually add those founding dates for each company. Would this work for you?

Regards