YTD data into MTD/YTD report

Question

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!

Alteryx issue.xlsx

Alteryx soln2.xlsx

clmc9601 · Answer

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!

ROHANPACH MTD YTD.yxzp

ROHANPACH · Answer

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

Alteryx soln2.xlsx

clmc9601 · Answer

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 MTD YTD.yxzp