AVG over Group Max in Pivot Table

[#1]

Hello,

I have created a pivot table over complaints. Any compaint may have multiple actions. Every action has a start and an end date, for which the difference is calculated. For every complaint the biggest difference should be displayed in the group total. In the total over all complaints i need the average over all group totals.

I attached a sample. For the complaint 10 is the biggest difference 9 Days and for the complaint 20 is biggest difference 8 days. In the total over all values is currently the biggest value from all groups displayed, 9. But I need the average over all group totals, (9 + 😎 / 2 = 8,5

Has anyvone an idea, how I can solve this problem?

[Anonymous]

The dimensionality() function can help you. This tells you what level of dimension a cell is being calulcated at. You can test if it is 0, and do an average, otherwise do the max you were doing.

See attached.

Jonathan

[#3]

Hello Jonathan,

thank you for your quick answer, this is helpflul.

But I need the average only for the group totals (lines MAX). Currently the average is calculated for all values and not only for the biggest values of each complaint.

The max value from the complaint 10 is 9 and the max value from the complaint 20 is 8.

The result should be 8,5 (9+8/2)

Alexander

[Anonymous · Accepted solution]

You'll need to use an aggr() function in this case, to test for the max of each of your Compaints.

See attached. I've changed the script to add a Duration field to make it easier to do the expression.

Jonathan

[#5]

Many thanks Jonathan.

This is exactly the solution I was looking for