Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

SUM/COUNT values for every year in a time span

timol
8 - Asteroid

Dear all,

 

I have the following data structure

 

A112.01.2020
A212.01.2020
A2312.01.2020
A2112.01.2019
A2112.01.2018
A2112.01.2015
A2112.01.2015
B2312.01.2019
B4212.01.2020
B2312.01.2019
C112.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.

 

A201542
A201642
A201742
A201863
A201984
A2020110

 

 

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

Thank you so much!

 

13 REPLIES 13
afv2688
16 - Nebula
16 - Nebula

Hello @timol ,

 

does this work for you?

 

Untitled.png

 

Regards

timol
8 - Asteroid

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)

 

A201542
A201642
A201742
A201863
A201984
A2020110
afv2688
16 - Nebula
16 - Nebula

Hello @timol ,

 

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

 

Untitled.png

 

Regards

 

timol
8 - Asteroid

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
16 - Nebula
16 - Nebula

Hello @timol ,

 

Could you please explain what is wrong with this solution?

 

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

 

Untitled.png

 

Regards

timol
8 - Asteroid

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
A2015112.01.2020
A2015212.01.2020
A20152312.01.2020
A20152112.01.2019
A20152112.01.2018
A20152112.01.2015
A20152112.01.2015
B20192312.01.2019
B20194212.01.2020
B20192312.01.2019
C2016112.01.2020
C2016212.01.2020
C20162312.01.2020
C20162112.01.2019
C20162112.01.2018
C20162113.01.2018
C20162114.01.2018
afv2688
16 - Nebula
16 - Nebula

Hello @timol ,

 

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

 

Regards

timol
8 - Asteroid

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
16 - Nebula
16 - Nebula

Hello @timol ,

 

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

Untitled.png

 

Regards

Labels