Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
tomhovens
Contributor II
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 SegmentProjects per Segment

I got per Day the amount of items per project

ProjectItemsProjectItems

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

Maximum Per Day and Segment.PNG

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).

Maximum per Item per Segment.PNG

Does anyone have an idea to solve this problem?

 

Thanks in advance

Labels (2)
9 Replies
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
Contributor II
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.

sunny_talwar

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

tomhovens
Contributor II
Contributor II
Author

Yes sure, no problem. Please see the attached files

Maximum Graphic.png

sunny_talwar

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

tomhovens
Contributor II
Contributor II
Author

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
Contributor II
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

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))