Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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.
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.
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
Just read Karl's post ... monthname(DATE) is better than my way! 🙂
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')
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.