topic pivot table grand total Problem in QlikView

pivot table grand total Problem

viveksairam89 — Wed, 27 Jan 2016 13:27:35 GMT

rank	Name	item	Month	Jan	Feb	Mar	Jun	Aug	Grand Total
2	abc	123		1.57	0	3.40	0.50	1.47	6.93
7	cdf	456		0	1.14	0	0	0	81.63
8	qwe	789		0	81.63	0	0	0	1.14
Total				1.57	41.39	3.40	0.50	1.47	14.95 should come

Can any one help me only the highlighted in red color should calculate and total should be 1.57+1.14+81.63+3.40+0.50+1.47/6=14.95

other totals are correct month wise

used expression:

avg(Aggr(DISTINCT sum(Value)/Count(if(Name<>0,Name,Month)), Name))

Regards

vivek

Re: pivot table grand total Problem

sunny_talwar — Wed, 27 Jan 2016 13:35:37 GMT

May be attach a sample to look at if possible.

Re: pivot table grand total Problem

veidlburkhard — Wed, 27 Jan 2016 14:21:24 GMT

Hi vivek,

please use the below expression and you will get your favored result.

If(Dimensionality() = 0, Sum(Aggr(DISTINCT sum(Value), item, Month))/Count(if(Value<>0,Month)), Sum(Value))

Hope this helps.

Burkhard

Re: pivot table grand total Problem

viveksairam89 — Wed, 27 Jan 2016 18:03:49 GMT

@Sunny T

i have attached the sample QVW

Thanks

Re: pivot table grand total Problem

viveksairam89 — Thu, 28 Jan 2016 07:04:46 GMT

Hi,

Can you help me out I have attached the sample QVW and attached screen shot

Iam not getting the exact grand total 14.95

Thanks

Re: pivot table grand total Problem

jagan — Thu, 28 Jan 2016 08:20:41 GMT

Hi,

Try this expression

If(RangeMax(Dimensionality(), SecondaryDimensionality()) = 0,

Sum(TOTAL value)/Sum(Aggr(If(Sum(value) > 0, 1, 0), month, item, Name, rank)), Sum(value))

I think it is 15.58.

Regards,

jagan.

Re: pivot table grand total Problem

viveksairam89 — Thu, 28 Jan 2016 09:03:05 GMT

I am not getting the corret answer for grand total and inside the dimension values also changed using the expression

can you help me out by seeing the attached screen shot and qvw to help to understand better

Thanks in advance

Re: pivot table grand total Problem

viveksairam89 — Thu, 28 Jan 2016 09:25:04 GMT

Hi all,

Using this expression we can achieve this output

Sum(Aggr(Avg(value), month, item))/ Sum(Aggr(Count(DISTINCT {<value -= {'0'}>} item),item,month))

rank	item	Month	Jan	Feb	Mar	Jun	Aug	Grand Total
2	abc	123	1.57	0	3.4	0.5	1.47	1.73
7	cdf	456	0	1.14	0	0	0	1.14
8	qwe	789	0	81.63	0	0	0	81.63
Total			1.57	41.39	3.4	0.5	1.47	14.95