Need help to find out current year - previous year

[MinaN]

Hi 
I am a new to alteryx.

I have data set like below

Region	Source	Name	Start Date	end date	year	month	Quarter	Type	Auto/Manual
NA	abc	xyz	03/02/2023	03/03/2023	2023	3	1	a	auto
UK	abc	abc	11/09/2023	11/24/2023	2023	11	4	b	auto
Ire	abc	cve	12/04/2022	12/30/2022	2022	12	4	c	manual
SA	abc	wer	01/02/2024	01/14/2024	2024	1	1	r	manual
NA	abc	wfvds	08/20/2024	08/20/2024	2024	8	3	a	auto
Ire	abc	xyx	09/12/2023	10/02/2023	2023	10	4	c	auto
UK	abc	abc	01/23/2025	01/31/2025	2025	1	1	x	manual
UK	abc	xyz	02/03/2025	02/07/2025	2025	2	1	a	auto

So in alteryx I need to create a pivot table using the above table-

Filters

• Name

Columns

• Year
• Quarter

Rows

• Auto/Manual
• Type

value

• Avg of date diff start & close date
• avg total of Current year - previous year

I am not being able to find out "Current year - previous year". It has to be dynamic. If we are in 2026, current year should be 2026 & previous should be 2025 and in column "Current year - previous year" should only show avg total of 2026-avg total of 2025

If someone help me would be very helpful.

Thanks in advanced

[Pilsner]

Hello @MinaN 

If you are looking to dynamically select information relating to the current year and previous year only, I would suggest using a filter tool with the following configuration:

 

Regarding the average time between the start / end data, i would first convert the dates from a string to a date type using the date time tool. After this use a formula tool with the following configuration to find the difference. Note you can change the interval to "months" or "years", instead of "days". You can then use the summarise tool to find the average.

As for the pivot table, I am not quite sure what you are after. If you are able to provide an example schema for the output you would like I would be happy to assist further. Based on my current understanding though it sounds like you want to use the Crosstab tool. In here you can select the year column under "change column headers" and then any other column as the "Values for new column". If the re are other columns that you wish to keep, you will need to select them in the "Group data by these values" section.

Below I have attached a workflow which contains all the steps I have taken.

Please let me know how you get on

Regards - Pilsner

[MinaN]

Hi Pilsner, 
Thanks for your support.
I have to include previous years if any like 2022, 2023 etc. Could you please help me with that?

[Pilsner · Accepted solution]

Hello @MinaN 

If you are lookign to match every year, with the values from the previous year, you may want to try the following.

Take the data and add a prefix of "previous" to each column. Then create a new column using the formula tool with the following configuration.

After that, you will want to join this new table, back to the original table, on the year column. You may also want to include Region or Source in the join, depending on your desired output. Do you have an example output table that I could try and work towards?

Please let me know how you get on with this new approach, I have attached the workflow below.

Regards - Pilsner