Group data

[#1]

Hello!! After two hors without success, some idea how show this?
	DATA	DATE	NAME	AMOUNT
		15/09/2010	GEORGE	40
		11/10/2010	JANE	10
		19/10/2010	GEORGE	115
		28/10/2010	GEORGE	900
		01/11/2010	JANE	256
		12/11/2010	JANE	200
		14/12/2010	GEORGE	100
		23/12/2010	JANE	25
	I need show this.
		Sep-10	Oct-10	Nov-10	Dec-2010	Total
	JANE	0	10	456	25	491
	GEORGE	40	1015	0	100	1155
	Thanks Regards César Estrada

[pover · Accepted solution]

1. Create a pivot table with two dimensions:

Name, monthname(DATE)

and one expression

sum(Amount)

2. Expand the first column of the pivot table.

3. Drag the second column of the pivot table to above the expression column (sometimes this is a little tricky to do) and when a horizontal blue arrow appears let go of the mouse.

4. You have your report.

Regards.

[Miguel_Angel_Baeyens]

Hi César,

Do a pivot table with NAME and

=Date(DATE, 'MMM-YY')

as Dimensions and

Sum(AMOUNT)

You will have to pivot the month columns.

Hope this helps.

[#4]

Hi,

Do this as a pivot table with Name and Date as dimensions and then sum(Amount) as the expression.

To get the months to group like that, edit the Date dimension like this:

=MONTH(DATE)&'-'&YEAR(DATE)

Hope that helps!

Cheers, Emma

[#5]

Just read Karl's post ... monthname(DATE) is better than my way! 🙂

[#6]

not sure using the date format will work - when I've tried it before this just gives me several columns for each month rather than totalling them up. Only works for me if I include monthstart as well ... then the formula becomes

=DATE(MONTHSTART(DATE),'MMM-YYYY')

[#7]

Thanks!!!! work perfect!!!!

Regards

César

[pover]

Agreeing with Emma, the date function usually maintains the original number value of the date, even though the format changes. I've had this problem in the script.

Regards.