Current and previous month selection

[PN2527]

I have the data in following input format:

Year	Month	Code	Value
2020	1	Code1	1.2726
2020	2	Code1	4.4541
2020	3	Code1	2.5452
2020	4	Code1	3.8178
2020	5	Code1	3.8178
2020	6	Code1	16.03476
2019	9	Code1	1.2726
2019	10	Code1	0.50904
2019	11	Code1	35.6328
2019	12	Code1	7.6356

and I need to output it in the following format:

Here, the current previous month's value should change with the change in a month and the previous should get calculated automatically.

Code	Current Month	Prior Month	Change $	Change %	Jun 20	May 20	April 20	March 20	February 20	January 20	December 19	November 19	October 19	September 19
Code1	16.03476	3.8178	12.21696	320	16.03476	3.8178	4.8006	2.5452	4.4541	1.2726	7.6356	35.6328	0.50904	1.2726

[T_Willins]

Hi @PN2527,

The attached workflow is dynamic based on the current date.  The top branch of the workflow compares dates in the data to the current date and labels the current and prior months.  Filter, Cross Tab, Formula and Dynamic Rename tools to get the first part of your required data.  The bottom branch Labels, Sorts, and Formats via Cross tab the remaining desired fields.  A Dynamic Rename ensures the data is in the correct order by using a Record ID tool as the initial label, then replacing that field name with the correct field name.  An Append Fields brings it all together.

[PN2527]

@T_Willins 

Thanks for the reply.

I believe record id logic is not working in actual data where I have data of period - 2012 to 2020 and several codes. Also, in the bottom cross tab tool it's giving the following error:

Error: Cross Tab (454): At least one CrossTab Method must be specified (Not counting Total Row)

[T_Willins · Accepted solution]

 Hi @PN2527,

The second Cross Tab method should be either First, Last or Sum - all give the same result as the data is one-for-one.  With additional codes, a couple of modifications were needed to aggregate the data correctly.  I added a second code with sample data so you can see how it will look.

[PN2527]

It works great. Thank you so much.

[Bluesky]

I have a similar need.

I need to pull 2 consecutive months but when it comes to pulling Dec 2020 and Jan 2021, there is an error.  The year isn't changing. I'm trying to capture the year from either current date or file path.  Please help. Thank you

[T_Willins]

HI @Bluesky,

If the data is in the same format as the data this workflow should work.  Can you provide a sample of your data?

[Bluesky]

I am pulling current and future months of data under several layers of folder structures to pull a several excel tabs from a work book.  Because there is some lag time, I want to pull 2 months of consecutive data but dynamically pull the month and year from current date

Filepath structure example:   N:\\Maps\DT YEAR\Bucket\Jan 2020\[Filename]

                                                              XX ####               

right now, I am using the latest creation date and want to dynamically "hardcode" the two consecutive months based on current date.  

Current date month:  ToDate(DateTimeAdd(datetimetoday(),0,"month"))

Previous Month: ToDate(DateTimeAdd([date],-1,"month"))

However, I need to pull 2 consecutive months and when it comes to pulling Dec 2020 and Jan 2021, there is an error.  The year isn't changing. I'm trying to capture the year from either current date or file path.  I don't know how to dynamically do this - I am setting this on a financial year Oct 1 to Sep 30

if ToNumber(DateTimeFormat(date,"%M"))>9
THEN
(ToNumber(DateTimeFormat([Date],"%Y"))+1)
else
(DateTimeFormat([Date],"%Y"))
ENDif

Lastly, using the latest / max creation date has it's own issues, e.g. if there is a later file and the previous month's workbook was not updated before reading, it will be skipped, hence the two months.  I tried to dynamically alter the filepath using a conditional if and RegEx statement to replace the current month with current month -1 but it didn't work.

Thanks