Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggregation issue

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 facilityMaterial IDMaterial needs last yearMaterial needs this yearEstimated needs ( =Max(this year, last year)
1xx100120120
1xy806080
1xz507070
2xx408080
2xy503050
2xz402040

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 IDEstimated needs ( =Max(this year, last year)
xx200
xy130
xz110

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

1 Solution

Accepted Solutions
sunny_talwar

This may be?

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

Capture.PNG

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

View solution in original post

5 Replies
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]))


talk is cheap, supply exceeds demand
Not applicable
Author

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

This may be?

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

Capture.PNG

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