Problem with pivot table measurement sub total

venkatasuresh — Wed, 25 Nov 2020 16:16:04 GMT

Hi Exports,

Im getting sum of subtotal wrong in pivot table FC AVG COGS column, here im getting total -0.14 its wrong count its suppose to be -64.86 -right one, Please find the below pivot table for your ref.

BMC Market	Posting Period	CY AVG Sales to thirds	FC AVG Sales to thirds	CY AVG COGS	FC AVG COGS	numerator	denominator	num/den
DACH	1	27.09	27,092.32	13.22	-13.22	-20,324.96	1,536.90	-13.22464081
DACH	2	28.03	28,034.52	13.71	-13.71	-20,133.71	1,468.67	-13.70881123
DACH	3	28.46	28,458.52	12.77	-12.77	-27,253.17	2,133.52	-12.77382571
DACH	4	25.67	25,671.16	12.04	-12.04	-19,274.00	1,600.98	-12.03890858
DACH	5	27.99	27,988.64	12.96	-12.96	-20,750.48	1,600.51	-12.96494983
DACH	6	25.28	42.28	11.97	-0.02	-2,411.64	126,276.00	-0.01909818
DACH	7	21.52	42.94	9.84	-0.02	-1,880.74	92,365.00	-0.02036208
DACH	8	26.99	44.21	12.52	-0.02	-2,312.70	112,326.00	-0.020589193
DACH	9	-	44.75	-	-0.02	-2,783.65	134,661.00	-0.020671519
DACH	10	-	52.06	-	-0.02	-3,095.36	127,397.00	-0.024296936
DACH	11	-	57.37	-	-0.03	-3,897.66	149,098.00	-0.02614158
DACH	12	-	48.05	-	-0.02	-3,350.60	151,396.00	-0.02213136
DACH	Total	26.10	26,098.18	12.21	-0.14	-127,468.68	901,859.57	-0.141339827
Total		26.10	26,098.18	12.21	-0.14	-127,468.68	901,859.57

-127,468.68/901,859.57=-64.86 right answer

-0.14 now - error value

expression:

(Sum({<[FISCPER]={'>=$(VMinFCPeriod)'}>}[Cost of Sales FC]) + Sum({<[FISCPER]={'<$(VMinFCPeriod)'}>}[Cost of sales.CS_TRN_GC]/1000))/

(sum({<[FISCPER]={'>=$(VMinFCPeriod)'}>}[Quantity])+sum({<[FISCPER]={'<$(VMinFCPeriod)'}>}[Sales to thirds./BIC/NQUANTITY_ORIGIN]/1000))

the above expression im using to generate FC AVG COGS values

along with for your reference i have created numerator denominator fields you can do the division with numerator/denominator(-127,468.68/901,859.57=-64.86 right answer)

can you any one help to resolve this issue.

topic Problem with pivot table measurement sub total in QlikView

Problem with pivot table measurement sub total