Have the maximum values depending on the dimensions

Question

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. Thank you in advance,

Issam Moalla · Accepted Answer

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)Formula Wizard access buttonThe 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.Maximum ValueThe 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

Anja · Answer

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

Have the maximum values depending on the dimensions

2 replies

Reply

Ask your question here!

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

Reply

Ask your question here!

Not finding what you're looking for?

Sign up

Single Sign-On Need help?

Log into the Pigment Community

Single Sign-On Need help?

Scanning file for viruses.

This file cannot be downloaded