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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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.

Highlighted
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