Nested Aggregation

[tomhovens]

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?

Thanks in advance

[sunny_talwar]

Try this expression

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

[tomhovens]

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.

[sunny_talwar]

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

[tomhovens]

Yes sure, no problem. Please see the attached files

[sunny_talwar]

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

[tomhovens]

I added the segment, the projectid is different

[sunny_talwar]

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

[tomhovens]

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

[sunny_talwar]

How about this

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