Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Converting year to date (YTD) to monthly amounts

r_kest
5 - Atom

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)?

3 REPLIES 3
zajaccount
9 - Comet

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_kest
5 - Atom

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.

zajaccount
9 - Comet

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.

Labels