YTD (cumulative) data to MTD data

[ROHANPACH]

Hello experts

I have large data sets in a cumulative way like different files on YTD basis. So for example - multiple files for Jan YTD, Feb YTD and so on.

By Union tool I can make 1 file since the structure of all the files will be same. How do I get the data for individual months and probably automate this for future months as well? 

Request your help on this!

[RolandSchubert]

Hi @ROHANPACH ,

I think, a Sort tool and a Multi-Row Formula tool should do the job.

First sort your data (by account,code and period), the calculate the difference between row (current month accumulated) and previous row (previous month accumulated), difference should be the monthly value.  If value for previous month is NULL, it is the first month for the current accent, so monthly value = ytd

 

Let me know if it works for you.

Best,

Roland

[Qiu]

@ROHANPACH 
Can you give us a sample of your desired output? 😁

[ROHANPACH]

Sorry, should also have given the desired output. Basically I want the dates in columns with both MTD & YTD section separately  

[ROHANPACH]

Please find attached

[ROHANPACH]

Thanks! @RolandSchubert 

On running the flow, it is not matching with I am expecting,

for eg. Account C Code S214 I should see +43 in Feb and -43 in March. not seeing in your file.

Have attached the expected solution for your reference as well.

[RolandSchubert]

Hi @ROHANPACH ,

I'm a bit confused ...  in your Excel file, there are two rows for account "C" and code "S214":

In Alteryx, sorting data by [Account], [Code] and [Period] returns:

 

Is there an additional condition or criterion to sort by?

Best,

Roland

[ROHANPACH]

Hello @RolandSchubert 

I get it now! you are right, I had an issue in my solution file. Just 1 more question - can we have the data with MTD month columns and then YTD month columns?

[RolandSchubert]

Hi @ROHANPACH ,

using the Cross-Tab tool you can move amounts to columns. I added rename to create month names as headers and used Join tool to conect monthly and accumulated values.

Best,

Roland

[ROHANPACH]

Hi @RolandSchubert 

The structure is perfect! but I think we have an issue in the MTD calculation.

For Eg. Account A & Code S234 Feb YTD is 0 so Feb month should be -40.. since Jan is 40.. right? but we have 0

I think we are missing a formula somewhere.