Dear all,
I have the following data structure
Now I would like to just count the Expenses for a total year to get the structure below
Does anyone have an idea how to do that?
In excel I would work with a COUNTIFS function.
Thank you so much!
Hi @timol,
I have prepared a workflow for you.
The first summarize shows sum:
The second one show countif:
Thank you!!!
Thank you so much!
Sorry but One more question: How can I tell Alteryx to create a new column filled with the value of column "A", but if column A is null it should take the value of column "B"?
If you want to grab the number from what’s underneath, you could use a Muti Row formula tool.
if the value is blank, look to the row below.
you’d likely need to sort by year and then by company to make this method work.
let me know if that helps.
cheers!
esther
Thank you so much!
So there is no way using just the "normal" formula tool stating like in excel =IF(B1;"";C1;B1) ("if B1 is empty use C1 otherwise use B1)?
You absolutely could, if the data for A, B, C were in the same row. It's when they're not in the same row that we need to get into the multi row formula tool
Cheers!
Esther
Would it also be possible to count values for e.g. 2019 and all years before that?
So assume that in the table above I would have data for the years 2015-2020. And 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.
So assume in the example below I would like to show values for 2016 and 2017 too (that are constant in comparison to 2015).
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 |
Does anyone have an idea?
The output for eg. company A I would like to achieve would look like this:
A | 2015 | 42 |
A | 2016 | 42 |
A | 2017 | 42 |
A | 2018 | 63 |
A | 2019 | 84 |
A | 2020 | 110 |