topic Re: Average in Pivot Total instead of sum in App Development

Average in Pivot Total instead of sum

sangram_01 — Wed, 13 Nov 2019 06:42:28 GMT

I have a pivot table which displays quarter wise count of employees, I want to show averages in stead of sum in Total of pivot table , how to have average in total instead of sum ?

I tired using Aggr function as 'avg(aggr(count(Emp),FYQ))' but it does not give appropriate result

Re: Average in Pivot Total instead of sum

Anil_Babu_Samineni — Wed, 13 Nov 2019 06:56:27 GMT

The number is correct to me. Where is the wrong for you?

Re: Average in Pivot Total instead of sum

sangram_01 — Wed, 13 Nov 2019 07:50:13 GMT

It is manually modified in column 3 after exporting in the Excel

Re: Average in Pivot Total instead of sum

Gopinathan — Wed, 13 Nov 2019 07:54:06 GMT

Hi,

Please use the dimensionality() function to write the average expression TOTAL rows.

For Example, if(Dimensionality()=0, avg(Value), sum(value)) in the expression. Dimensionality() function will return 0,1,2,.. based on the number of dimensions in the pivot table.

Thanks,

Gopi

Re: Average in Pivot Total instead of sum

sangram_01 — Wed, 13 Nov 2019 08:02:31 GMT

Thanks Gopi, but it gives result as 1 instead of 2503

Re: Average in Pivot Total instead of sum

Gopinathan — Wed, 13 Nov 2019 08:22:47 GMT

Share the sample data and the expression used like count(employee).

Re: Average in Pivot Total instead of sum

Gopinathan — Wed, 13 Nov 2019 09:05:00 GMT

consider your dimension is Period and the expression is count(Employee) than the expression should be in the pivot table as,

if(Dimensionality()=0,Avg(aggr(Count(Employee),Period)), Count(Employee))

Re: Average in Pivot Total instead of sum

sangram_01 — Wed, 13 Nov 2019 09:19:14 GMT

Great, Thanks this worked.