5 Replies Latest reply: Oct 21, 2015 11:22 AM by Sunny Talwar RSS

    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)


      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)



      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