Pivot Table Month by Month

[betz]

Hello,

How do I achieve the following layout?

I want to sum and count the sales per month.

For example the data looks like this:

LOAD

Product,

SalesDate,

Amount,

Quantity

As in the following image, I assume it should be a PIVOT table so I need the SalesDate to be a dimension, but how do I make each month a dimension like in the image?

[Or]

You would add a Month field to your load, e.g.

LOAD

Product,

SalesDate,

MonthName(SalesDate) as SalesMonth,

Amount,

Quantity

[betz]

Yes, but would it show the dimensions as I described like in the image I've provided?

[qv_testing]

Try this

LOAD Category,
Description,
Month(Date)&'-'&Year(Date) as MonthYear,
Qty,
Amount
FROM tableName;

[barnabyd]

G'day @betz,

The solution from @qv_testing, while it will give you the correct output, it will sort alphabetically, which you don't want.

The solution from @Or, which uses the MonthName() function, gives you the correct date format and the correct sorting.

To achieve the dimensions as in your diagram, you need to use the Pivot chart, so that you can add your dimensions to both rows and columns.

Cheers, Barnaby.

[qv_testing]

create one field for sort like below

Year(Date)*100+(Num(Month(Date))) as Sort,

and apply on chat sort expression.