topic Re: How to add calculated column from Total in pivot table? in QlikView

How to add calculated column from Total in pivot table?

Fri, 26 Feb 2016 14:36:25 GMT

Hi,

I have pivot table as below. I need additional column Balance next to Total and it should calculate from Total-Tar

I am not getting column next to Total. please can anyone suggest me how to do this. I do not have possibility to use cross table.

data	Tar	D	19/02/2016	22/02/2016	23/02/2016	24/02/2016	25/02/2016	Total
AA	32		4	3	5	3	1	16
DD	32		1	4	3	2	0	10
ADS	32		1	4	2	2	0	9
dfs	32		100%	100%	67%	100%		90%
gfd	32		3	8	3	0	8	22
wed	32		4	8	6	2	11	30
sdf	32		2	10	0	11	5	28
hgf	32		7	10	13	8	9	25
edf	32		0	0	0	0	0	0
rew	32		1	0	0	0	1	2

Re: How to add calculated column from Total in pivot table?

alexandros17 — Fri, 26 Feb 2016 14:38:39 GMT

I think this is not possible, total is automatically calculated before or after all columns (in the dimension).

Re: How to add calculated column from Total in pivot table?

Fri, 26 Feb 2016 14:39:48 GMT

is there any possibility in any other way apart from pivot table?

Re: How to add calculated column from Total in pivot table?

marcus_sommer — Fri, 26 Feb 2016 14:47:54 GMT

You could add such a column if you added a dimension-value Balance to your date-field (maybe you used two identically fields - one for normal usage and one for this chart and then with Balance) and refers within the expression to them, like:

if(date = 'Balance', sum(total value) - Tar, sum(value))

or you could add simply a second horizontally dimension to your date-field, maybe month and queries this quite similar to the above if-loop with:

if(secondarydimensionality() = 0, sum(total value) - Tar, sum(value))

- Marcus

Re: How to add calculated column from Total in pivot table?

Fri, 26 Feb 2016 15:05:39 GMT

Tried it . not showing values in column.

what do I need to pass in place of value? in above expressions?

Re: How to add calculated column from Total in pivot table?

marcus_sommer — Fri, 26 Feb 2016 15:16:15 GMT

sum(value) is a placeholder for YourExpression and you will need the total-version of them to get the sum over all dates.

- Marcus

Re: How to add calculated column from Total in pivot table?

Fri, 26 Feb 2016 15:26:08 GMT

sorry I am not getting how to replace my expression with all dates

Re: How to add calculated column from Total in pivot table?

marcus_sommer — Fri, 26 Feb 2016 16:02:38 GMT

Please provide a small example app with a few inline-data.

- Marcus