Using maxOver and Sum

Question & Answer
,
1

Hi,

I have the attached dataset structure and I want to figure out how to calculate the aggregation of total_number_of_nights_for_travel per company_id.
I tried using sum(maxOver(number_of_nights_for_travel, [travel_id],PRE_AGG)) but it just gave me a summary of the max value for each travel_id multiplied by the number of record appearance (as number of charge_id)

company_id trans_id travel_id number_of_nights_for_travel total_number_of_nights_for_travel
AA 111 12 8 25
AA 222 12 8 25
AA 333 12 8 25
AA 444 12 8 25
AA 555 15 17 25
AA 666 15 17 25
AA 777 15 17 25
BB 888 17 21 21
BB 999 17 21 21

Any ideas?

Thanks!

2

Hi @amico
what would be your expectation for company id aa? Because you are using max and sum.
But I would say you have to group on company level.
BR

3

Hi @ErikG

I would expect getting the numbers written in the last column total_number_of_nights_for_travel.
The problem is I get the 83 (32+51), which is the aggregation of maximum number of nights per travel multiplied by the number of appearnces of each maximum value as a result of its existance in each transaction.

Thank you