topic Re: Pivot Table Month by Month in App Development

Pivot Table Month by Month

betz — Sun, 17 Mar 2024 11:11:37 GMT

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?

Re: Pivot Table Month by Month

Or — Sun, 17 Mar 2024 13:57:31 GMT

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

LOAD

Product,

SalesDate,

MonthName(SalesDate) as SalesMonth,

Amount,

Quantity

Re: Pivot Table Month by Month

betz — Sun, 17 Mar 2024 14:15:30 GMT

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

Re: Pivot Table Month by Month

Nagaraju_KCS — Sun, 17 Mar 2024 15:36:02 GMT

Try this

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

Re: Pivot Table Month by Month

barnabyd — Sun, 17 Mar 2024 20:53:58 GMT

G'day @betz,

The solution from @Nagaraju_KCS, 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.

Re: Pivot Table Month by Month

Nagaraju_KCS — Mon, 18 Mar 2024 03:26:32 GMT

create one field for sort like below

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

and apply on chat sort expression.