Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Nested Aggregation

Hi All,

I'm facing a problem regarding nested aggregations.

I'm seeking a solution to find the following:

I got Projects per Segment

Projects per Segment

I got per Day the amount of items per project

ProjectItems

What I do is to calculate per day per item the sum (also per segment)

Now do I need the maximum per item and of that day the amount per segment (green rows above). With the function I use I only get the maximum per segment (please see the not correct output table below).

Does anyone have an idea to solve this problem?

Labels (2)

• ### nested

9 Replies
MVP

Try this expression

`Sum(Aggr(If(Max(TOTAL <Item> Aggr(Sum(Amount), Item, Date)) = Sum(TOTAL <Item, Date> Amount), Sum(Amount)), Item, Date, Segment))`
Contributor II
Author

Thanks Sunny, it works for most of all items. Only for items which has more than one day of max it's totalizing. Do you have an solution to that. It tried to work with count(distinct date). But it don't work.

MVP

Would you be able to share an example of the scenario you just mentioned?

Contributor II
Author

Yes sure, no problem. Please see the attached files

MVP

Project have different values then what was given earlier. What are the new Segment values associated with the new Project Numbers

Contributor II
Author

I added the segment, the projectid is different

MVP

Okay what is the expected output based on this new data that you have provided?

Contributor II
Author

The expected output should be 10224 for segment 1, the other segments doesn't have any value. Problem is that the max amount per day is during from 25-06 untill 02-07. The function that you provided me summarize the 7 days

MVP

`Sum(DISTINCT Aggr(   If(Max(TOTAL <Item> Aggr(Sum(Amount), Item, Date)) = Sum(TOTAL <Item, Date> Amount)   , Sum(Amount)), Item, Date, Segment))`