community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

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

Atom

Hi 

 

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 

 

 

 

 

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.

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.

...

Labels