5 Replies Latest reply: Dec 29, 2017 3:52 PM by Sunny Talwar RSS

    Aggregating data for subsets in QlikSense

    Jacob Wapinsky

      Hi all,

       

      I've been working on trying to aggregate data in a certain way for use as a consumption reporting tool

      I have not been able to come up with a way that works to show consumption the way I am being asked to show it.

       

      1) The data - here is an example of the data set I am working with.

              

      Year MonthAreaProduct TypeProduct IDProduct Material AdditionMaterial IdentifierMaterial SuplierTest TypeTest Result
      20171aaa1aa14000xxxxxx0.14
      20171aaa1aa14000xxxxxy0.5
      20171aaa1aa14000xxxxxz0.36
      20171aaa1aa13700xxyxyx0.15
      20171aaa1aa13700xxyxyy0.52
      20171aaa1aa13700xxyxyz0.33
      20171aaa1aa500yyyyxx0.75
      20171aaa1aa500yyyyxy0.25
      20171baa1aa6000zzzzzx0.5
      20171baa1aa6000zzzzzy0.5
      20171aaa1ab40000xxxxxx0.14
      20171aaa1ab40000xxxxxy0.5
      20171aaa1ab40000xxxxxz0.36
      20171aaa1ab1000yyyyxx0.75
      20171aaa1ab1000yyyyxy0.25
      20171abb1ba10yyyyxx0.75
      20171abb1ba10yyyyxy0.25
      20172aaa2aa40000xxyxyx0.15
      20172aaa2aa40000xxyxyy0.52
      20172aaa2aa40000xxyxyz0.33
      20172baa2aa7000zzzzzx0.5
      20172baa2aa7000zzzzzy0.5
      20172abb2ba70yyyyxx0.75
      20172abb2ba70yyyyxy0.25


      2) Output Desired:  Ultimately what I need is a summation of the column Product Material Addition divided by the count of distinct product ID's  such that I get an averaged consumption for an aggregation on material, product type, area, and/or date in any combinations.

      ex. Aggregation on product type and material identifier

           

      Product TypeMaterial IdentifierProduct Material Addition# of distinct Product IDConsumption
      aax3231003107700
      aay300031000
      aaz2600038666.666667

       

      Using the formula sum([Product Material Addition])/count(distinct [Product ID]) results in getting a sum of the material additions when the material is used as shown below

           

      Product TypeMaterial IdentifierProduct Material Addition# of distinct Product IDConsumption
      aax3231003107700
      aay300021500
      aaz260002

      13000

       

      I've also attempted to use sum([Product Material Addition])/count(total distinct [Product ID]) however that results in a total number that does not change with respect to the desired aggregation

           

      Product TypeMaterial IdentifierProduct Material Addition# of total distinct Product IDConsumption
      aax323100564620
      aay30005600
      aaz2600055200

       

       

      Any help would be appreciated in solving this problem.