Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
venkatasreekant
Valued Contributor

Avgs columns in Pivot table

Hi Qlikers,

I am facing a problems. Its related to pivot table.

In pivot table right now i can able to show sum(sales) by region and monthyear wise bifurcation.

Now my problem is i want to get the Averages for region wise As show in below image & highlighted in red color.


  

Region2016-122017-012017-022017-032017-042017-052017-062017-072017-082017-092017-102017-112017-12AVG
East3584715865766117147377841,1965,1815,1323,7673,8121,840
West2142682953045227687096981,1055,3775,9784,8944,0961,941
South3554134034534975915335319466,8068,7158,1697,4072,755
North1,9132,8262,9743,3703,2433,9674,0014,3279,20668,88395,21688,50080,85028,406

Dimensions : Region, MonthYear

Expression : Sum(Sales)


Kindly help on this how to achieve.


Thanks in Advance...

1 Solution

Accepted Solutions

Re: Avgs columns in Pivot table

Look at the attached app and image....

My mistake when I said that add partial sum for Region... you needed to add it from MonthYear

Capture.PNG

View solution in original post

9 Replies

Re: Avgs columns in Pivot table

May be try this

Avg(Aggr(Sum(Sales), Region, MonthYear))


and enable show partial sum for Region

venkatasreekant
Valued Contributor

Re: Avgs columns in Pivot table

Hi Sunny,

Thanks for the prompt response. But the expression is not worked for me.

For better understand, i want to show case the Sum(sales) & Avg's both are in same pivot table.

Kindly refer the table which i have attached in the original post.


Thanks & Regards,

Venkata Sreekanth

Re: Avgs columns in Pivot table

The expression I gave you should do that for you... is it not doing that? Would you be able to share a sample file where you are trying this out?

venkatasreekant
Valued Contributor

Re: Avgs columns in Pivot table

Hi Sunny,

Please find the sample application for your reference.


Thanks & Regards,

Venkata Sreekanth


Re: Avgs columns in Pivot table

Look at the attached app and image....

My mistake when I said that add partial sum for Region... you needed to add it from MonthYear

Capture.PNG

View solution in original post

venkatasreekant
Valued Contributor

Re: Avgs columns in Pivot table

Hi Sunny,


Thanks for your response. With this sample application, it is solved my problem.


Thanks once again.


Thanks & Regards,

Venkata Sreekanth

venkatasreekant
Valued Contributor

Re: Avgs columns in Pivot table

Hi Sunny,

I'm sorry for this , again opening the same thread.


Till now we are good getting the AVG's on the end of the table. Now my another requirement  is i want to get the Totals which i was shown in the sample below.

I mean on the MonthYear wise sums we have to show along with the AVG's in the same table. Is this possible ?

RegionMonthYearDec 2016Jan 2017Feb 2017Mar 2017Apr 2017May 2017Jun 2017Jul 2017Aug 2017Sep 2017Oct 2017Nov 2017Dec 2017AVG
Central25,33521,6913,33515,5053,38311,58912,9678,8736,25317,34312,12215,15518,88313,264
East27,6455,3834,9697,70010,2316,11915,16112,32220,75832,41632,30545,63420,08418,518
South10,8974,8162,1836,6449,44810,9448,9343,30210,37210,20112,13728,71715,21010,293
West33,12212,0829,81529,02413,46015,60915,92020,76825,73827,90721,21228,94229,65221,788
Total96,99943,97120,30158,87236,52244,26152,98245,26463,12187,86777,777118,44883,829

Thanks & Regards,

Venkata Sreekanth

Re: Avgs columns in Pivot table

Try this

=If(SecondaryDimensionality() = 0, Avg(Aggr(Sum(Sales), Region, MonthYear)), Sum(Sales))


Capture.PNG

venkatasreekant
Valued Contributor

Re: Avgs columns in Pivot table

Hi Sunny,


Thanks for your prompt response. The condition is working properly


Thanks & Regards,

Venkata Sreekanth