Finding Best week per customer in a year

[Djain1984]

We want to calculate best week for each customer. Best week is based on the the highest amount accumulated in 52 weeks (in a year). We have used Agg, Sum, Max but still not able to get the results. 

Example, say we have 10 customers and each customer we have 52 weeks (1 year) of Values and we have 5 years of data for each customer, now each week per customer we will have a different value. So what we really want is Best week value per year or for 5 years per customer.

Result will look like below:

Customer A best week is 32 and the value is 500

Customer B best week is 12 and the value is 300

Customer C best week is 51 and the value is 600

Table will look like below:

Week, Year, Customer name, Value

1, 2024, A, 100

2, 2024, A, 200

1, 2023, A, 100

2, 2023, A, 400

3, 2023, A, 500

1, 2022, B, 100

2, 2024, B, 200

Thanks Guys !

[Padma123]

firstsortedvalue([Customer name],aggr(max(Value),[Customer name],Year))

[tresesco]

Try like:

Object : table
Dim 1: Customer
Dim 2: Year
Exp1(Best Week) : FirstSortedValue(Week, -Value)
Exp2(Best Value) : Max(Value) 

When you have multiple transactions in a week, you have to use sum() in combination with aggr