Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Djain1984
Contributor
Contributor

Finding Best week per customer in a year

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 !

Labels (1)
2 Replies
Padma123
Creator
Creator

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

tresesco
MVP
MVP

Try like:


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

tresesco_0-1724740240947.png

 


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