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