How could I combine these 2 charts/calculations?

[josephinetedesc]

Hi

I have used set analysis and was able to make these 2 pivot tables.  However I would like to combine them.

so the top chart is by Month,Classification and Location - the bottom chart is by Month and Classification.

I tried usng the partial sums but that didn't work. 

Thank you

Jo

[swuehl]

A pivot table would calculate the partial sums as expression total, and I assume you want to have something else.

What expressions have you used in both charts?

[Anonymous]

If you are having additional Dimension like you said Location in ablove table:

I wud say, you can add below table expression to above one but it wud further bi furcate w.r.t location.

the only difference is in one table you are watching this report with Month and Location & in other table it is with Month only.

[buzzy996]

if u share ur .qvw with expected output that helps to quickly respond,could u pls share the same?

[josephinetedesc]

the formula i use is

sum(DurMins)/Sum({1}AvailMins)

The dimensions for this is just Date

The dimensions for this is Date and Location

[swuehl]

Ok, and using the partial sums, what results do you get and what do you expect to get instead (AKA 'didn't work')?

[josephinetedesc]

I want it to look like this - ie the last column would be Total ...

Date	Location	a	b	c	d	e		Date	Machine Down
Apr 2015	42095	3.2%	0.8%	1.2%	2.0%	0.4%		Apr 2015	1.5%
May 2015	42125	0.4%	0.6%	1.3%	4.5%	0.9%		May 2015	1.9%
Jun 2015	42156	3.0%	1.4%	3.0%	2.5%	1.2%		Jun 2015	2.5%
Jul 2015	42186	1.6%	4.2%	2.2%	2.2%	2.8%		Jul 2015	2.5%
Aug 2015	42217	11.4%	0.7%	2.0%	3.4%	0.1%		Aug 2015	3.2%
Sep 2015	42248	1.7%	1.7%	1.6%	1.7%	0.1%		Sep 2015	1.4%
Oct 2015	42278	0.6%	2.0%	1.6%	2.0%	0.7%		Oct 2015	1.5%
Nov 2015	42309	-	0.6%	0.8%	0.7%	0.2%		Nov 2015	0.6%

This is what I get when if do a partial sum on Date - which looking at now must be a running total per Location:

Date	Loca	Locb	Locc	Locd	Loce
Total	1.2%	0.7%	0.8%	1.1%	0.4%
Apr 2015	3.2%	0.8%	1.2%	2.0%	0.4%
May 2015	0.4%	0.6%	1.3%	4.5%	0.9%
Jun 2015	3.0%	1.4%	3.0%	2.5%	1.2%
Jul 2015	1.6%	4.2%	2.2%	2.2%	2.8%
Aug 2015	11.4%	0.7%	2.0%	3.4%	0.1%
Sep 2015	1.7%	1.7%	1.6%	1.7%	0.1%
Oct 2015	0.6%	2.0%	1.6%	2.0%	0.7%
Nov 2015	-	0.6%	0.8%	0.7%	0.2%

but if I do a partial sum on date and location I get this which is perfect  so  it is working beautifully

Date	Location	Total	loca	Locb	Locc	locd	loce
Total		0.8%	1.2%	0.7%	0.8%	1.1%	0.4%
Apr 2015	#	1.5%	3.2%	0.8%	1.2%	2.0%	0.4%
May 2015	#	1.9%	0.4%	0.6%	1.3%	4.5%	0.9%
Jun 2015	#	2.5%	3.0%	1.4%	3.0%	2.5%	1.2%
Jul 2015	#	2.5%	1.6%	4.2%	2.2%	2.2%	2.8%
Aug 2015	#	3.2%	11.4%	0.7%	2.0%	3.4%	0.1%
Sep 2015	#	1.4%	1.7%	1.7%	1.6%	1.7%	0.1%
Oct 2015	#	1.5%	0.6%	2.0%	1.6%	2.0%	0.7%
Nov 2015	#	0.6%	-	0.6%	0.8%	0.7%	0.2%

Thank you all - solved

[josephinetedesc]

the only thing to make it absolutely perfect is if i could bold the "total" column and rows ... but maybe that is a question for visualisation group!

[swuehl]

It should already be bold, at least in most of the table styles. You can let your visualization group handle this, or you can enable design grid in View menu, then right click the pivot table chart and select 'custom format cell'. Here you can make the total row / column bold.

[josephinetedesc]

What I had to do was to do: Control G (this is the grid) then when you click on the cell you have format cell available.

Jo