topic Re: Aggr Function in App Development

Aggr Function

pascaldijkshoor — Tue, 03 Apr 2018 13:38:48 GMT

Hi Guys,

I am struggling with an aggr function. I think in theory the formula i use should be right but somehow it does not work. I am calculating the driven kilometers of trucks with the following formula: Max (Mileage) - Min (Mileage), and this works fine. Every truck is linked to a department. Now i want to make a pivottable with department as a dimension and a certain timefield (date/week/month etc.) as a dimension.

The measure should calculate for every truck separately the driven kilometers and then calculate the average of all trucks per department. I tried to use this formula but it doesn't work:

avg (aggr ((Max (MILEAGE)-Min (MILEAGE)),ASSET_NR_LINKED))

Whereby ASSET_NR_LINKED is the dimension for the different trucks.

Anyone who can help me?

Re: Aggr Function

sunny_talwar — Tue, 03 Apr 2018 13:42:42 GMT

Could a single truck land up in more then one Department or is there a one to one relation between Department and truck? If it is not one to one, then may add Department to your Aggr() function

Avg(Aggr((Max(MILEAGE) - Min(MILEAGE)), Department, ASSET_NR_LINKED))

Re: Aggr Function

pascaldijkshoor — Tue, 03 Apr 2018 13:47:18 GMT

No a single truck is only linked to one department, so this is not the case.

Re: Aggr Function

sunny_talwar — Tue, 03 Apr 2018 13:48:10 GMT

Have you still tried this?

Avg(Aggr((Max(MILEAGE) - Min(MILEAGE)), Department, ASSET_NR_LINKED))

Re: Aggr Function

pascaldijkshoor — Tue, 03 Apr 2018 13:52:44 GMT

Yeah i tried, but the result is exactly the same.

Re: Aggr Function

marcus_sommer — Tue, 03 Apr 2018 13:55:39 GMT

You mentioned that the expression should work in context with "... certain timefield (date/week/month etc.) ...". This meant you need also to add these time-fields to your aggr. If this didn't work it would be helpful to know with which numbers do you expect which output and which you get from your expression.

- Marcus

Re: Aggr Function

pascaldijkshoor — Tue, 03 Apr 2018 14:17:40 GMT

Thanks Marcus, the addition of the timefield seems to solve the problem