YTD data into MTD/YTD report

[ROHANPACH]

Hello experts,

I have a data which is on a YTD basis(monthly) (like 2021.01, 2021.03 etc.). The dates are currently in rows. 

For my end report to the user - I need to have monthly dates in columns with a section for MTD (Jan MTD, Feb MTD etc) as well as YTD (Jan YTD, Feb YTD etc)

Have attached the extract of existing data set and expected output

Please help!

[randreag]

hi @ROHANPACH 

You can calculate first the MTD and YTD, assign the respective names (ex, Jan MTD, Feb MTD) and then use the transpose in order to leave them as columns

I hope it helps

[ROHANPACH]

Hi @randreag 

Can you please show how to calculate MTD for the data I sent? I am not an expert in Alteryx.

It would be great if you could share the workflow. As for Jan YTD = Jan MTD. Am trying but not able to solve this.

Thanks in advance,

Rohan

[clmc9601]

Hi @ROHANPACH,

Does this achieve your desired output? Crosstab automatically sorts columns into alphabetical order-- what aruiz-itp said would have worked, but I just chose to let Crosstab sort the months by number and change them to names afterward.

You can customize the grouping/concatenation in the summarize tool. 

If this is helpful, please consider marking it as a solution so others may find it!

[ROHANPACH]

Hi @clmc9601 

Sorry, but not sure if this works. The data I sent was on YTD basis which means Apr YTD = Jan to Apr. Therefore Mar MTD = Mar YTD - Feb YTD. 

Just to give you an example - for the given line (all parameters being same) Apr MTD should be 98834 - 54406 = 44428

whereas in the solution we see YTD numbers only

Please also refer to my solution sheet where I want to see both MTD and YTD columns .

Sorry for not being clear earlier.

Thanks.
Rohan

[clmc9601]

Hi @ROHANPACH,

Thank you for clarifying! I adjusted the solution to calculate MTD properly. Is this what you were going for?

If this helps, please consider marking it a solution so others may find it. Thanks!