Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

nasjw3255
New Contributor II

Aggregating data for subsets in QlikSense

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Aggregating data for subsets in QlikSense

Try this

=Sum([Product Material Addition])/Count(TOTAL <[Product Type]> DISTINCT [Product ID])

5 Replies
nasjw3255
New Contributor II

Re: Aggregating data for subsets in QlikSense

As another question

How can I aggregate the total movements to give myself a % of total movements?

I.e

     

Product TypeMaterial IdentifierProduct Material AdditionaggregatedConsumption
aax3231003521000.917637035
aay30003521000.008520307
aaz260003521000.073842658

when i use the Aggr(sum([product material addition]),Product Type) function  it only returns data for the first value.

MVP
MVP

Re: Aggregating data for subsets in QlikSense

Try this

=Sum([Product Material Addition])/Count(TOTAL <[Product Type]> DISTINCT [Product ID])

MVP
MVP

Re: Aggregating data for subsets in QlikSense

May be this

=Sum([Product Material Addition])/Sum(TOTAL <[Product Type]> [Product Material Addition])


Capture.PNG

nasjw3255
New Contributor II

Re: Aggregating data for subsets in QlikSense

Am I able to put multiple parameters into the <> where product type is?

I.e. if I want month and product type?

MVP
MVP

Re: Aggregating data for subsets in QlikSense

Yes you can

Community Browser