Converting year to date (YTD) to monthly amounts

I have raw trial balance data that is currently monthly in columns (Jan, Feb, Mar, etc) shown as YTD results for a total of three annual periods (i.e. 2017, 2018, 2019). I am looking for an alteryx formula that can calculate the monthly values within each fiscal year. For example Feb =Feb - Jan, Mar = Mar - Feb etc. How can I perform this calculation without calculating January 2018 as (Jan-18 - Dec-17)?

Dynamic Processing

Preparation

Accepted answers

zajaccount

I think I misunderstood at first - I think this could be possible by doing the following:

1) transpose your data so that you have a date and the value column

2) extract the year using the datetimeformat function

3) filter by year

4) use the multi row formula to calculate the differences between the months

5) union the resuts.

Please find an adjusted workflow attached.

R_total.yxmd

All comments

zajaccount

Hi,

you could transpose your data so that you have a "Month" column and a "Value" column and then use the running total tool.

As to the years, you could extract the year from the date using the datetimeformat function and then use the "year" as the "group by" in the running total tool - please find the sample flow attached.

R_total.yxmd

r_kest

zajaccount, thanks for the response, would this workflow work the opposite way...? So in your example if 11/2019 = 10 and 12/2019 = 15 and 1/2020 = 5, I could calculate 12/2019 = 5 and 1/2020 = 5 etc.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703