Solved

# Have the maximum values ​​depending on the dimensions

• 2 replies
• 76 views

• Newly Minted Pigmenteer

Hello,

I have a transaction list with month, employee, role and value like this :

 Month Employee Role Value oct-23 1000 RA 12 nov-23 1000 RA 23 dec-23 1000 RA 12 oct-23 1001 RB 34 nov-23 1001 RB 32 dec-23 1001 RB 21 oct-23 1002 RA 45 nov-23 1002 RA 67 dec-23 1002 RB 54 oct-23 1003 RB 32

Each column (whitout value) is connected to a dimension. I would like to have the max of a value by role but I would like to can change the month.

Ex : If I select no month in the table(or all), I would like to see :

 RA 67 RB 54

And if I select nov-23 :

 RA 67 RB 32

Same things if I select oct-23 and nov-23 :

 RA 67 RB 54

I am newbiee in Pigment and I don’t know how to do this with functions. At the end I would like to let the user choose one or many months and one or many employees and see the max value in one month by role.

icon

Best answer by Issam Moalla 13 December 2023, 17:29

View original

### 2 replies

Userlevel 5
+9

Hi @Anja ,

What you are looking to apply here is an aggregation of the values by Role and Month.
In order to do that you would apply the BY Modifier with the MAX as an aggregation method.
So your formula would be :

``'Employees transaction'.Value[BY MAX: 'Employees transaction'.Role, 'Employees transaction'.Month]``

This would result in having the maximum value by Role and by Month.
If you like to practice more the aggregations metrics from transaction list, you can use the Formula Wizard which will walk you step by step in creating the formula. (You can access it through the formula playground)

The next step would be Pivoting the table and adjusting the Summary to be set on Max for the time dimension in order to give the output you are looking for.

The Max aggregator set on the summary would be looking for the maximum value across the selected months in your page filter.

Hope this helps,
Issam

Thank you very much ! It works ! I didn’t use the max aggregator on the summary.