How to get Monthly Sales by Year CrossTab working

[akpolyd]

Hello,

I'm seeking to setup a new report that will show me the total sales per month (row) by each year (column).

Example of what I'm looking for:

Month 2021 2022 2023

______________________________

January 5 7 12

February 1 - 3

March 3 5 7

......

December 5 8 -

My table looks like this:

Order Date Order Number Order Total

January 3, 2021 0000001 1

January 4, 2021 0000002 4

February 9, 2021 0000003 1

March 1, 2021 0000004 2

March 18, 2021 0000005 1

January 3, 2022 0000006 2

January 4, 2022 0000007 5

March 1, 2022 0000008 3

March 18, 2022 0000009 2

January 3, 2023 0000010 6

January 4, 2023 0000011 6

February 9, 2023 0000012 3

March 1, 2023 0000013 3

March 18, 2023 0000014 4

I have attempted this by creating a crosstab with order date (grouped by each year) as the columns and order total as the summary field. However, when I apply the same order date (grouped by month) as the row it brings in every month (january 2021, january 2022, january 2023) instead of just January.

Is anyone able to assist with displaying the table as the example above which the 12 months as the rows, and each year in the data as the columns. The field summarized is the sum of the order total for that month in that year 1+4 for January 2021.

Appreciate any help you can give!

Thank you!

[moshenaveh]

Welcome to the SAP Community! Thank you for visiting us to get answers to your questions.

Since you're asking a question here for the first time, I'd like to offer some friendly advice on how to get the most out of your community membership and experience.

First, please see https://community.sap.com/resources/questions-and-answers, as this resource page provides tips for preparing questions that draw responses from our members. Secondly, feel free to take our Q&A tutorial at https://developers.sap.com/tutorials/community-qa.html as well, as that will help you when submitting questions to the community.

Finally, I recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

[DellSC · Accepted solution]

Try this:

1. Create two formulas:

{@OrderMonth}
Month({MyTable.OrderDate})

{@OrderYear}
Year({MyTable.OrderDate})

These will give you the month number and year number. You need the month number to get the cross-tab to sort correctly.

2. Suppress the details section in your report if all you're going to display is the cross-tab

3. Create your cross-tab using {@OrderMonth} as the row, {@OrderYear} as the column, and sum of OrderTotal as the summary.

4. After placing the cross-tab in either a header or footer section of your report, right-click on the row header and select Row Options >> Group Options. Go to the Options tab and check the "Customize Group Name Field" checkbox. Select "Use Formula as Group Name" and then click on the formula button to the right of that option. Use something like the following formula to get the month names instead of the numbers:

Switch(
{@OrderMonth} = 1, 'January',
{@OrderMonth} = 2, 'February',
{@OrderMonth} = 3, 'March',
{@OrderMonth} = 4, 'April',
{@OrderMonth} = 5, 'May',
{@OrderMonth} = 6, 'June',
{@OrderMonth} = 7, 'July',
{@OrderMonth} = 8, 'August',
{@OrderMonth} = 9, 'September',
{@OrderMonth} = 10, 'October',
{@OrderMonth} = 11, 'November',
{@OrderMonth} = 12, 'December',
)

-Dell

[akpolyd · Accepted solution]

Thank you so much Dell, your answer worked flawlessly!