Skip to Content
0
Sep 19, 2023 at 12:50 PM

How to get Monthly Sales by Year CrossTab working

43 Views

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!