Avg / Aggr with 2 dimensions

zarmoton — Wed, 21 Feb 2024 14:04:28 GMT

Please i try to have a pivot table doing this calculation (an average of a group by date). see my data below:

Site ID	Month	Date	Port	Util
1	February	19-févr	10/1	0%
2	February	19-févr	10/1	0%
3	February	19-févr	10/2	5%
1	February	20-févr	10/6	0%
2	February	20-févr	10/1	0%
3	February	20-févr	10/1	0%
4	February	20-févr	10/2	5%
5	February	20-févr	10/3	10%

How to calculate :
Monthly Average of Nb of distinct Port per day (by Util)
Util	0%	5%	10%
19-févr	1	1
20-févr	2	1	1
avg	1.5	1	0.5

Expected pivot table :
	February
Util	0%	5%	10%
Avg	1.5	1	0.5

What is the good setanalysis to do that ?

of course if i have some data by days on March, we need to see separatly from february.

(group by Month in fact)

Re: Avg / Aggr with 2 dimensions

LRuCelver — Wed, 21 Feb 2024 14:55:24 GMT

Sum(Aggr(Count(distinct Port), Date, Util)) / Count(distinct total Date)