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: 
Anonymous
Not applicable

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
sunny_talwar

Try this

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

View solution in original post

5 Replies
Anonymous
Not applicable
Author

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.

sunny_talwar

Try this

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

sunny_talwar

May be this

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


Capture.PNG

Anonymous
Not applicable
Author

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

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

sunny_talwar

Yes you can