[ASK] percentage - subtotal - result not correct

[#1]

hello QV World...

i have problem with subtotal Percentage

below is the pivot table i've created in QV n it doesn't work

jenis_dgps	ket_jenis_dgps	Summary	Percentage	Percentage 0	Percentage 1
pendidikan terakhir	≤ SLTA	11671	2.306 %	6.876%	0.023
	D1	327	0.065 %	0.193%	0.001
	D2	69594	13.751 %	41.004%	0.138
	D3	4919	0.972 %	2.898%	0.010
	D4	66	0.013 %	0.039%	0.000
	S1	80068	15.821 %	47.176%	0.158
	S2	1529	0.302 %	0.901%	0.003
	S3	10	0.002 %	0.006%	0.000
	≥S2	1539	0.304 %	0.907%	0.003
	Subtotal	169723	33.536 %	100.000%
status pegawai	NIP-13	14389	2.843 %	8.556%	0.028
	NIP-15	91938	18.166 %	54.665%	0.182
	NIP Daerah	8924	1.763 %	5.306%	0.018
	Non-PNS	52933	10.459 %	31.473%	0.105
	Subtotal	168184	33.232 %	100.000%
tempat mengajar	TK	1563	0.309 %	0.929%	0.003
	SD	122667	24.238 %	72.936%	0.242
	SMP	26699	5.276 %	15.875%	0.053
	SDLB	204	0.040 %	0.121%	0.000
	SMPLB	40	0.008 %	0.024%	0.000
	SMA	11079	2.189 %	6.587%	0.022
	SMK	5913	1.168 %	3.516%	0.012
	SMALB	19	0.004 %	0.011%	0.000
	Subtotal	168184	33.232 %	100.000%
Total		506091	100.000 %	100.000%

dimension are :

1. jenis_dgps

2. ket_jenis_dgps

expressions :

1. summary : sum(sumz)

2. Percentage : sum(sumz)/sum(Total sumz)

3. Percentage 0 : Sum(sumz)/Sum(Total <jenis_dgps> sumz)

4. Percentage 1 :

pick(dimensionality(), null()

, sum(sumz)/sum(Total sumz)

, Sum(sumz)/Sum(Total <jenis_dgps> sumz)

)

the problem is..

expression percentage is for Total percentage

expression percentage 0 is for subtotal percentage

expression Percentage 1 is for both

but Percentage 1 doesn't show like it have to show (wrong result)

question :

what's wrong with my syntax?

i really need helps

many many thx..

[mike_garcia]

I think you only need to add a "+1" to your Pick Expression's first argument.

pick(dimensionality() + 1
     , null()
     , sum(sumz)/sum(Total sumz)
     , Sum(sumz)/Sum(Total <jenis_dgps> sumz)
   )

... That, and apply the correct format.

Mike.

[#3]

thx Mig..

it worked

[#4]

hello mig..

i triied ur solution, but there's no result for Total

what is pick() function format?

[#5]

solved

pick(dimensionality()+1

     , Sum(sumz)/Sum(Total <jenis_dgps> sumz)

     , Sum(sumz)/Sum(Total sumz)

     , Sum(sumz)/Sum(Total <jenis_dgps> sumz)

     , Sum(sumz)/Sum(Total <jenis_dgps,ket_jenis_dgps> sumz)

   )

result :

jenis_dgps	ket_jenis_dgps	Nama_Prov	Jumlah	Percentage
pendidikan terakhir	≤ SLTA		11671	6.876%
	D1		327	0.193%
	D2		69594	41.004%
	D3		4919	2.898%
	D4		66	0.039%
	S1		80068	47.176%
	S2		1529	0.901%
	S3	Daerah Khusus Ibukota Jakarta	3	30.000%
		Jawa Barat	1	10.000%
		Kalimantan Selatan	1	10.000%
		Nusa Tenggara Barat	5	50.000%
		Sub-Subtotal	10	0.006%
	≥S2		1539	0.907%
	Subtotal		169723	33.536%
status pegawai			168184	33.232%
tempat mengajar			168184	33.232%
Total			506091	100.000%