pivot table grand total Problem

[viveksairam89]

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

[sunny_talwar]

May be attach a sample to look at if possible.

[veidlburkhard]

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

[viveksairam89]

@Sunny T

     i have attached the sample QVW

Thanks

[viveksairam89]

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

[jagan]

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.

[viveksairam89]

Hi

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

[viveksairam89 · Accepted solution]

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