How to divide outputs of two fields?

Question & Answer
1

Hi all! Not sure how to go about dividing in calculated fields
sum( dateDiff({date},{list_of_dates},"DD") / count(list_of_dates) gives this error:
Nesting of aggregate functions like SUM and SUM(DATEDIFF(“date”, “list_of_dates”, _UTF16’DD’) / NULLIF(COUNT(“list_of_dates”), 0)) is not allowed.
I have a list of dates1 that I want to datediff against a date2. However this list of dates1 was pulled with a query that duplicated the entire data each time so each date2 has a list of date1 - This is fine but datediff will return the correct value multiplied by the amount of dates1 which I want to revert
Any thoughts on this would be great!
If there’s a solution to prevent duplication when querying that may be even better and fix all the root issues!!

2

There are two issues with your expression: sum(dateDiff() / count())

  1. count() is inside sum(), making it a nesting of aggregation
  2. even without sum(), dateDiff() / count() is an operation of non-aggregated field divided by aggregated field, which is also not allowed
1 Like