Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

How to get MTd numbers from each file which has YTD numbers

5 - Atom



I have 12 files from Jan to Dec. Each file contains YTD numbers. 


if i wanted to look at March revenue  actual then it is      March - (Jan + Feb) = March Revenue actual


now i need to get MTD numbers to each month till Dec. 


Thanks in Advance 





16 - Nebula
16 - Nebula

I suggest something like the following.


- Put the YTD data into a single table with the filename as well (an option in the Input tool allows this)

- Derive a date field using a formula tool parsing the filename

- Sort by date

- You can then create MTD using a MultiRow formula computing difference of [YTD] and [Row-1:YTD]


Hope that makes sense.

6 - Meteoroid

This was very helpful. I have a similar problem in converting YTD amounts to MTD amounts. The formula "[Value] - [Row-1:Value]" worked after sorting the file, however, I have many different accounts (i.e., Revenue, expenses, etc). How do I limit the formula to calculate the MTD for each account name and for each year.


For example, I do not want the formula to cross over years or accounts. See below:


Account        Date          YTD     MTD

Revenue    12/1/2016       10         10        

Revenue    1/1/2017         12         12         (Note the change in year from 2016 to 2017, so the formula should start over on this row)

Revenue    2/1/2017         20          8

Revenue    3/1/2017         29          9 

Expense    12/1/2016        5           5          (Change in account)

Expense    1/1/2017          9           4          (Change in year)

Expense    2/1/2017        14           5



Appreciate the help.