Creating a sum of data from a specific year for each relevant name

Question

I have been given a Large set of data of multiple companies with invoices from different dates throughout 2021,22,23. Attached is the Test data on excel.

I am trying to simply have an output where it shows the Company name, Description of the company and then the total invoice value for 2022.  So it looks like something below;

CHICKEN FARM Signage - temp£232.61Melon FarmStructural Engineers£503,628,63

I've tried simply filtering out the 2021 and 2023 data and creating a running total for the data remaining but I can't seem to format the data correctly or really even make much progress with this. I am pretty new to Alteryx as well so sorry if this is a simple question any help would be greatly appreciated.

Test Alteryx Data.xlsx

danilang · Accepted Answer

Hi @T3skComplete2023

These kinds of reports are easy for humans to read and understand and a pain for computers to parse.

You have to use the structure of the report to determine how to parse it.  In this case the farm name is preceded and followed by a null row.  The work type is preceded by a null and followed by a non-null period.  Use a couple of Multi Row Formula tools to mark these and then get the location from the first row of each farm and join to the rest of that farm's data.  Remove the null period rows and parse the year from the remaining periods.  Sum the invoices by location, work and period to give you

Dan