topic Re: Nested Aggregation in QlikView

Nested Aggregation

tomhovens — Sat, 16 Nov 2024 04:44:03 GMT

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

Re: Nested Aggregation

sunny_talwar — Thu, 17 Jan 2019 12:51:42 GMT

Try this expression

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

Re: Nested Aggregation

tomhovens — Mon, 21 Jan 2019 07:30:24 GMT

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.

Re: Nested Aggregation

sunny_talwar — Tue, 22 Jan 2019 12:23:39 GMT

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

Re: Nested Aggregation

tomhovens — Tue, 22 Jan 2019 13:46:14 GMT

Yes sure, no problem. Please see the attached files

Re: Nested Aggregation

sunny_talwar — Tue, 22 Jan 2019 14:11:09 GMT

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

Re: Nested Aggregation

tomhovens — Tue, 22 Jan 2019 14:22:58 GMT

I added the segment, the projectid is different

Re: Nested Aggregation

sunny_talwar — Tue, 22 Jan 2019 14:38:48 GMT

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

Re: Nested Aggregation

tomhovens — Tue, 22 Jan 2019 15:01:15 GMT

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

Re: Nested Aggregation

sunny_talwar — Tue, 22 Jan 2019 15:18:06 GMT

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