Calculated Field for year-to-month figure

whoiswct

New member
Joined
Nov 24, 2012
Messages
57
Reaction score
0
Points
0
I have a raw data which will be updated each week by other department and I can only use it but I cannot edit the original. So, I call the Pivot table to connect it as external source.

The raw data consists every month revenue figure (numeric) from last year Jan to current month.
e.g. Field:
Jan2011, Feb2011, Mar2011, ... , Dec2011
Jan2012, Feb2012, Mar2012, ... , Current_Month2012
The raw data file already pre-set Jan2012 to Dec2012 fields but no figure until the month is past.

I need to calculate Sum of Year-to-month figure, so I use Calculated Field.
e.g. Now is Nov 2012 and thus I have the figure up to Oct.
Sum of year-to-month (last year) = Jan2011 + Feb2011 + ... + Oct2011
Sum of year-to-month (this year) = Jan2012 + Feb2012 + ... + Oct2012

My problem is:
I need to modified the Calculated Field every month by adding the current month field to the formula.
Any automation I can do?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
What version of Excel are you using? 2007 or 2010?

Personally, what I'd do with it is to group the dates by year and by month, add a subtotal to the year, and avoid the calculated fields all together. Then use a filter to exclue the dates after the month that you want. Way easier to set up, and you only need to refresh the filter each time the month changes.
 

whoiswct

New member
Joined
Nov 24, 2012
Messages
57
Reaction score
0
Points
0
What version of Excel are you using? 2007 or 2010?

Personally, what I'd do with it is to group the dates by year and by month, add a subtotal to the year, and avoid the calculated fields all together. Then use a filter to exclue the dates after the month that you want. Way easier to set up, and you only need to refresh the filter each time the month changes.

excel 2010.
attached my sample file. since the fileds is arrange in column but not in row, so it seems cannot be group as suggested.
 

Attachments

  • sample(year to month)_excel2010.xlsx
    14.5 KB · Views: 28

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
The issue you've got is that your data is already coming into the PivotCache as a pivoted layout. Since it's the job of the pivottable to turn the data into the format you're bringing in, it's not going to work very well. If you can't change the data source at all, then the only option I can see for fixing this is to go with a macro to change it.

You keep telling us you can't change the data source, and that you can't add new columns, but I'd like to know a bit more here.

What are you connecting to in the other workbook? A table or a PivotTable?

Are you pulling the data directly into a PivotTable in this workbook, or are you pulling it into a table first, then re-summarizing it with a PivotTable?/
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Also, how to you grab the data, an exported file, a query, ADO, or what?
 

whoiswct

New member
Joined
Nov 24, 2012
Messages
57
Reaction score
0
Points
0
The issue you've got is that your data is already coming into the PivotCache as a pivoted layout. Since it's the job of the pivottable to turn the data into the format you're bringing in, it's not going to work very well. If you can't change the data source at all, then the only option I can see for fixing this is to go with a macro to change it.

You keep telling us you can't change the data source, and that you can't add new columns, but I'd like to know a bit more here.

What are you connecting to in the other workbook? A table or a PivotTable?

Are you pulling the data directly into a PivotTable in this workbook, or are you pulling it into a table first, then re-summarizing it with a PivotTable?/
Ans. I create a PT and direct connect to other excel file table.
Ans. Yes, I am pulling the data directly into a PT because I want to have latest data every time.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
I think the simplest way will be to write to macro that transposes the data, then pivot that new data.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
We're crossing posts and answers here. Based on what you've got in this thread, as well as what's in here...

Yes, I am pulling the data directly into a PT because I want to have latest data every time.

Pulling into a table is not mutually exclusive of updates. You could avoid a ton of these issues if you change your approach.

I'm guessing that you started this process by going to Insert-->PivotTable-->Use an External Data Source, is that correct?

Try this:
Go to Data --> Exisiting Connections
Your PivotTable's connection should be right at the top of the list. Click it and choose Open
Now, select "Table" and click OK

At this point, you'll get an official Excel table of the source data. And this table WILL update each time you open the file. You can now build your PivotTable using that table as the source.

May not look like you've accomplished anything different here, except adding an extra step, but that's not true. You've added versatility as you can add new columns to the table, and the formulas in them will automatically apply each time the data is updated.
 

whoiswct

New member
Joined
Nov 24, 2012
Messages
57
Reaction score
0
Points
0
We're crossing posts and answers here. Based on what you've got in this thread, as well as what's in here...



Pulling into a table is not mutually exclusive of updates. You could avoid a ton of these issues if you change your approach.

I'm guessing that you started this process by going to Insert-->PivotTable-->Use an External Data Source, is that correct?
YES.

Thanks for your solution. It's a prefect solution for my case and working fine with me.

Something minor but want some advice. The Table seems will not auto refresh and I need to click by hand (data>refresh all) even though I restart the workbook. Is there any config I need?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Sorry, didn't realize it doesn't set tables up by default. To fix that:

Go to Data-->Connections
Select the connection for your table. (If you're no longer using the old PIvotTable connections you may want to delete those, just don't delete any new ones you've set up.)
Click Properties
On the Usage tab, check the box beside "Refresh data when opening fthe file"
You can also schedule a regular refresh there too.
 
Top