Another Pivot table GETPIVOTDATA problem

RET79 · May 21st, 2002, 08:43 PM

I posted a query a while back about the GETPIVOTDATA function being reluctant to read in Data such as

GETPIVOTDATA(PivotTable1,"1 VA A 5 Sum of CURR-SUM-***")

because it had 2 fields with numbers in, you might recall I said that:

GETPIVOTDATA(PivotTable1,"1 VA A Sum of CURR-SUM-***")

worked no problems.

Well, sadly becuse of this problem I have had to restructure my pivot tables to now look like this (i.e. having some column fields instead of them all being row field, which I would have preferred as then I could have used the GETPIVOTDATA if it had worked to get what I wanted)

Microsoft Excel - 1-4NetPremPTgen.xls______________Running: xl2000 : OS = Windows (32-bit) 4.10
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
F7		=
	A	B	C	D	E	F
1			VAL-CLASS	DTI-TYPE	REC-TYPE
2			A
3			1
4	VAL-TYPE	Data	FPP	PP
5	5	Sum of NUM	1479780	109784
6		Sum of CURR-SUM-ASSD	27829721	5186524
7		Sum of ADDL-BEN	0	0
PivotSheet

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by[HtmlMaker V1.25]
If you want this code, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

On the worksheet I am setting up a function that picks up the maximum of CURR-SUM-ASSD and ADDL-BEN every time. Now I have to use the cell references to do this, I would rather use a MAX(GETPIVOTDATA(),GETPIVOTDATA()) thing for obvious reasons rather than MAX(D6:D7) say.

So basically, if I could get the GETPIVOTDATA() functions to read off when 2 fields are numbers then this would solve my problems and would mean I could set up my tables in the more desirable structure like this:

Microsoft Excel - 1-4NetPremPTgen.xls______________Running: xl2000 : OS = Windows (32-bit) 4.10
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
F7		=	0
	A	B	C	D	E	F
1	VAL-TYPE	VAL-CLASS	DTI-TYPE	REC-TYPE	Data	Total
2	5	A	1	FPP	Sum of NUM	1479780
3					Sum of CURR-SUM-ASSD	27829721
4					Sum of ADDL-BEN	0
5				PP	Sum of NUM	109784
6					Sum of CURR-SUM-ASSD	5186524
7					Sum of ADDL-BEN	0
PivotSheet

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by[HtmlMaker V1.25]
If you want this code, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

Many thanks,

RET79

PS. to Ivan and Colo, your HTML maker is absolutely superb, am proud to be using it!

[ This Message was edited by: RET79 on 2002-05-21 19:46 ]

RET79 · May 21st, 2002, 08:53 PM

Typical, with this small sample of the pivot table I have given you, it has actually worked! Maybe the problems occur with very large pivot tables?? Anyway, this is what I am hoping to achieve with the full pivot table

Microsoft Excel - 1-4NetPremPTgen.xls______________Running: xl2000 : OS = Windows (32-bit) 4.10
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
F14		=
	A	B	C	D	E	F
1	VAL-TYPE	VAL-CLASS	DTI-TYPE	REC-TYPE	Data	Total
2	5	A	1	FPP	Sum of NUM	1479780
3					Sum of CURR-SUM-ASSD	27829721
4					Sum of ADDL-BEN	0
5				PP	Sum of NUM	109784
6					Sum of CURR-SUM-ASSD	5186524
7					Sum of ADDL-BEN	0
8
9
10		:alert('=GETPIVOTDATA(A1,%225%20A%201%20FPP%20Sum%20of%20NUM%22)')>1479780
11		:alert('=GETPIVOTDATA(A1,%225%20A%201%20FPP%20Sum%20of%20CURR-SUM-ASSD%22)')>27829721
12
13		:alert('=MAX(GETPIVOTDATA(A1,%225%20A%201%20FPP%20Sum%20of%20NUM%22),GETPIVOTDATA(A1,%225%20A%201%20FPP%2 0Sum%20of%20CURR-SUM-ASSD%22))')>27829721
14		:alert('=MAX(F2,F3)')>27829721
PivotSheet

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by[HtmlMaker V1.25]
If you want this code, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

RET79

[ This Message was edited by: RET79 on 2002-05-21 19:56 ]

RET79 · May 21st, 2002, 08:59 PM

Just to avoid any confusion, although it makes no sense why I would want to pick out max of NUM and CURR-SUM-ASSD, I did that here since in the sample I had the ADDL-BEN field giving zero, so changed my mind from the first posting just to make sure it worked.

Sorry for any confusion, the main problem is reading the data consistently from the pivot tables.

RET79

[ This Message was edited by: RET79 on 2002-05-21 20:02 ]

RET79 · May 21st, 2002, 10:01 PM

OK, my latest guess is that maybe there is a link between this function not working consistently and the size of your pivot tables. My pivot tables are very large, got about 30 data fields summarised based on a set of data 8000 rows * 40 columns.
Typically 5 pivoting fields then the sum of 30 of the columns with 5 columns from the original data being left out of the pivot table layout. Also row and column totals not in my tables.

One of these days I will write a posting with no need for editing.....

RET79

[ This Message was edited by: RET79 on 2002-05-21 21:02 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 08:59 PM	#3
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	Just to avoid any confusion, although it makes no sense why I would want to pick out max of NUM and CURR-SUM-ASSD, I did that here since in the sample I had the ADDL-BEN field giving zero, so changed my mind from the first posting just to make sure it worked. Sorry for any confusion, the main problem is reading the data consistently from the pivot tables. RET79 [ This Message was edited by: RET79 on 2002-05-21 20:02 ]

May 21st, 2002, 10:01 PM	#4
RET79 Board Regular Join Date: Mar 2002 Location: England, UK. Posts: 526	OK, my latest guess is that maybe there is a link between this function not working consistently and the size of your pivot tables. My pivot tables are very large, got about 30 data fields summarised based on a set of data 8000 rows * 40 columns. Typically 5 pivoting fields then the sum of 30 of the columns with 5 columns from the original data being left out of the pivot table layout. Also row and column totals not in my tables. One of these days I will write a posting with no need for editing..... RET79 [ This Message was edited by: RET79 on 2002-05-21 21:02 ]