Nested aggregation not allowed

[Summiteer263]

Hi,

ich have a problem with an aggregation formula.

I have a list of countries, showing different dates and I want to know the "age in days" of this dates.

I have already created the quantity of days from the lates date.

for example: newes date in a list:  09.02.2020  => days between today and 09.02.2020 =>  52 days

used formula: Interval(today()-Date(Max(VALUE_DATE_LAST)))

Now I want to build the average of days by country (since many countries have more dates) Unfortunately I´m not able to find the correct aggr formula for this. Is there anybody who can help me with this problem? Thanks a lot in advance!

[tresesco]

May be something like:

Avg( Aggr(

Interval(today()-Date(Max(VALUE_DATE_LAST)))

, CountryDim))

[Summiteer263]

Thank you very much for your quick reply!
This was also my intention, but is the result is not correct
For example:
Country Belgium has 4 dates:
18.02.2020 => 43 days
29.10.2019 => 155 days
04.07.2019 => 272 days
So the average should be 157 days
The result of the formula: Avg( Aggr(Interval(today()-Date(Max(VALUE_DATE_LAST))), [Kunde Ländername]))
is 43 (always same as first record of each country)

[Summiteer263]

I guess that the formula tries to consider only the newest date for this calculation - in my case just the date with 43 days.  Since many countries have more than one device on site (Belgium has 3 devices), I need the newest date from every device and finally the average of all days.

any idea?

[Summiteer263 · Accepted solution]

I have found the solution! 🙂

as I have more than one device in one country I have to change the dimension into "devices" - instead of "countries"

Avg( Aggr(Interval(today()-Date(Max(VALUE_DATE_LAST))), [Ausprägung-SERIAL_NR]))

Anyway thanks again for your effort!