Excel - Pivot Table Fields ( moving multiple and format )

[bwatson]

Hi , Similar to my Charting issue , i have a large data set that has +- 50,000 rows and +- 365 columns.

1) I want to have each column reflected in the "value field" box but excel requires me to move each one at a time , why cant i cntrl/shift "end down" type manevour ,select all and move at once or use something else in excel to move all across ? 1 by 1 is time consuming

2) when i move the fields into this "box" it defaults to "summarise by count" how do i change this to default to "summarise by sum" especially as i dont want to be changing them 1 by 1 for 365 items !!

now you may say this is alot of data to be shown in point 1... which leads me onto point 3)

Similarly to my chart question , how can i get the pivot to by default exclude any fields where a condition is not met/ or met.

So i have a report filter that says only show the values for items that grew by > 20% however , this will still show me all the months for these items , how can only show the recent months.

So in example , if i have 50,000 prefixes , and 365 days of billing , based on my filter it should only show those that increased by > 20% for the day. ( so far ok excluding points 1 and 2 above ) but now what i want the report filter to do is only show the last 30 days data for these items , not alll 365. and i am certainly not going to go and change the value field on a daily basis to show 1st - 30th today , tomorrow 2nd to 31st , then 3rd to 1st , 4th to 2nd and so on by dragging items into and out of the value field box.

the reason i want to do this relates directly to my chart query , i as i can then ONLY chart what is then pivot'd on the screen which will be ONLY the last 30 days in for those prefixes where the billing went up > 20% and this may only be 5-20 rows x 30 days which is small in comparison to my 50,000 x 365 !!

Please help

 

[Fazza]

hi

1) Well, that is how it is. Though with VBA pretty much anything you want can be done.

2) Again, that is how it is. If the field is 100% populated in all records with numeric values the default is sum, otherwise count. Again, with VBA you can achieve what you want. (Hence, without using VBA & if suitable, populate all empty cells with zero entries then the field will default to sum.)

3) Suggest you do the filtering before the dataset reaches the pivot table. That is done using SQL. So, when setting up the pivot table choose the external data source at the first step of the wizard, ALT-D-P. Start in a new workbook. I can't be more specific without sample data as necessarily the SQL is - like program code - specific to the situation. Or use other methods to modify the source data before the pivot table, or maybe even have second pivot table that feeds back into the source data, or maybe utilise a database to do the querying.

hth

 

[Fazza]

Debra Dalgliesh's excellent site has an add-in that, amongst other functionality, looks like will change count to sum for mutliple fields. Untested.

http://www.contextures.com/xlPivotAddIn.html

 

[bwatson]

Thanks , i will give this add in a try

 

[bwatson]

Thanks the add in helps change all "count" to all "sum"


but i still cant move multiple fields into a box in one go ?

 

[Fazza]

try VBA for that: the macro recorder should get you part of the way there, then modify for complete solution. OK?

 

[Fazza]

Some more thoughts. This might be easier to do at the time of creating the pivot table. That way there will be simple connection between the fields and the destination pivot table. So, select the fields you want in the data range and have the code then create the pivot table.

I do wonder though about the efficacy of a pivot table with so many data fields: I suspect that reducing the dataset before creating the pivot table will be best.