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

4

Hello @amico, is this an issue you are still looking to resolve? I tested on my end, and I think the way to get the value you would want be to nest an avgOver function inside of a sumOver function. Try something like this:
sumOver(avgOver({number_of_nights_for_travel}, [{travel_id}], PRE_AGG), [{company_id}], PRE_AGG)

I’ll mark this as a solution for now, but if you still have trouble getting your desired output or used a different function to make this work, please let me know!