Pivot Tables : Multiple Summaries 1

[Loomah]

Hi
I've been asked to produce a report based on a set of data. the solution lies in creating a pivot table (or multiple tables) and a chart.

The data consists of, amongst other things, the date, a unique id, and a department name. the basis of the requirement is to report on the count of id by date by dept.

The tricky bit (that I don't know the answer to) is that there is a requirement to summarise the data quickly into weekly or monthly totals with a default of daily figures.

The obvious (to me anyway) solution is multiple tables - 1 for daily, 1 for weekly and 1 monthly but I feel the question "Can I not just have one table I can change with 1 click?" isn't far away! going down the multiple table route I'd group the dates as appropriate but is there a way to easily (and that's a key point, easily) swithch between the grouping in a single table?

Thanks in advance

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Hi,

Yes there is!

You can use the Group & Outline option within the PT for that particular NUMERIC field. Dates can be grouped by Days, Months, Quarters, Years and logical combinations threof (like Month & Year)

CAVEAT: The field MUST contain valid numeric data in EACH row of the data.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Loomah]

hi skip, thanks for this.

however i don't think i got the real issue across properly. i can already group the dates into week and month (with year to prevent jan 09 being grouped with jan 08 etc). the date field gives daily totals by default (obvious!!).

the real issue is, without creating 3 pts, is it possible to create a single pt that can be quickly and very easily switched from daily totals (default) to weekly totals (group) to monthly totals (group) and back again or from daily to monthly etc??

i could do it by changing the grouping myself but the issue is handing this over to someone who has minimal excel knowledge and is likely to just want to be able to "tick" daily, weekly or monthly to get the summary required without having to call me up.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[SkipVought]

Record a macro to do the changes.

Call them from a control, like maybe a set of option buttons or a combo--could be a Data > Validation LIST.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Loomah]

hi skip
really wanted to avoid the macro/vba route on this one but it looks likely to be the way to go.

thanks for the input!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[combo]

If 3 clicks are not too much and you can duplicate the date field in source data, next group the first field by year and month, the second one by (7) days. Drag required grouping as row field for instance, the unused one as page field.

combo