Aggregation issue

[#1]

Hi all,

I have a table where i calculate two columns across two dimensions, and where these two dimensions are the most detailed level of aggregation that my fact table has.

TABLE 1

Storage facility	Material ID	Material needs last year	Material needs this year	Estimated needs ( =Max(this year, last year)
1	xx	100	120	120
1	xy	80	60	80
1	xz	50	70	70
2	xx	40	80	80
2	xy	50	30	50
2	xz	40	20	40

Total needs by material ID:

xx - 200

xy - 130

xz - 110

I want to create a new table looking at the aggregate needs only, but i want it to yield the same results:

TABLE 2

Material ID	Estimated needs ( =Max(this year, last year)
xx	200
xy	130
xz	110

I use the same calculation condition in TABLE 2 as i did in TABLE 1,  I get:

the maximum of (the sum of needs last year on material ID level, the sum of needs this year on material ID level),

I want the sum of the maximum from each row as displayed in Table 1.

I cannot do this in the script due to the way I calculate the needs (includes a set analysis), thus I hope there is an aggr-function or anything else that makes me able to do this in qlik.

Thanks a lot

[sunny_talwar]

Wait I am confused, what exactly are you trying to do? Are you trying to change Table1 or Table2 above?? Can you share your expected output?

[Gysbert_Wassenaar]

Try;

sum(aggr(max(rangemax([Material needs last year],[Material needs this year])),[Storage facility]))

[#4]

Hei,

Imagine the data is structures as in Table 1.

I am simply trying to create the same table as Table 2

To do this, I need to firstly calculate the maximum of material needs this and last year

Then I need to summarize these figures by material ID to get the proper total estimated needs.

Better explained?

[sunny_talwar · Accepted solution]

This may be?

=Sum(Aggr(RangeMax([Last Year], [This Year]), [Storage facility], [Material ID]))

Renamed the fields, so here is the new script:

Table:

LOAD [Storage facility],

     [Material ID],

     [Material needs last year] as [Last Year],

     [Material needs this year] as [This Year]

FROM

[https://community.qlik.com/thread/188377]

(html, codepage is 1252, embedded labels, table is @1);

[sunny_talwar]

Hahahaha

I guess you got the solution you were looking for. I am glad you did figure it out

Best,

Sunny