Pivot Table with additional Columns

kushalthakral — Tue, 16 Aug 2016 11:58:30 GMT

Hi All

I am actually looking for additional columns in Pivot table so that i can show overall Total for 12 Months , Average and Percentage difference like below

Can any one has solution for this as i am aware you can not add additional columns, the other work around i am doing is having a separate straight table for 12 months total, average and percentage differences but i actually wanted to that in pivot table itself so if user wants to import he can import the entire 12 month data and total, avg and percentage diff

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan
	Test	Test	Test	Test	Test	Test	Test	Test	Test	Test	Test	Test	Test	12Month Total	12Mth Average
A	29	31	37	49	65	65	53	63	56	36	37	89	49	630	52.50
B	78	78	76	25	39	57	72	30	36	64	26	13	37	553	46.08
C	55	79	60	78	79	73	98	59	42	26	78	20	91	783	65.25
D	83	69	77	52	85	87	81	63	64	57	58	75	23	791	65.92
E	40	86	68	12	64	83	99	25	89	41	98	29	58	752	62.67
F	74	30	96	40	24	22	63	26	66	75	87	71	97	697	58.08
G	38	64	58	82	22	99	41	91	11	94	83	60	60	765	63.75
H	67	97	19	53	90	90	55	46	24	74	43	45	77	713	59.42
I	99	30	61	73	84	33	48	43	44	93	14	31	98	652	54.33
J	27	12	39	53	10	41	93	60	57	20	99	40	83	607	50.58
K	73	33	48	37	83	39	85	34	16	59	16	86	12	548	45.67
L	36	94	62	27	68	100	92	91	77	33	34	11	28	717	59.75
M	41	36	92	22	14	72	84	13	31	96	25	49	75	609	50.75
N	40	42	29	74	37	94	54	79	49	71	56	73	23	681	56.75
O	67	55	80	45	33	55	59	45	63	68	58	10	92	663	55.25
P	52	85	40	21	92	20	80	62	81	99	10	47	36	673	56.08
Q	69	25	70	98	74	17	98	10	64	73	90	74	57	750	62.50
R	90	34	32	43	16	82	65	79	89	63	15	30	28	576	48.00
S	41	25	30	63	41	10	54	79	81	85	43	87	35	633	52.75
T	24	71	23	56	30	32	11	26	14	52	76	19	40	450	37.50

Thanks

Kushal Thakral

Re: Pivot Table with additional Columns

marcus_sommer — Tue, 16 Aug 2016 13:04:17 GMT

The simplest way is to add a further horizontal dimension above your month-dimension (maybe Year) and then enabling partial sums for both horizontal dimensions and query them within your expression with:

if(secondarydimensionality() = 0, AvgExp, SumExp)

Otherwise you will need to create extra field-values for your month-dimension like sum and avg (maybe an additionally field only for these purpose) and query then which value your extended dimension had:

if(Month = 'avg', AvgExp, if( Month = 'sum', TotalSumExp, SumExp))

- Marcus

topic Re: Pivot Table with additional Columns in QlikView

Pivot Table with additional Columns

Re: Pivot Table with additional Columns