Skip to main content

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,

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'.ValueuBY 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 button


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.
 

Maximum Value

 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.

Reply


Ask your question here!

Terms & ConditionsCookie settings