Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Count Distinct if sum result is greater than X

Good Morning All,

I am new to Qlik Sense and liking it so far. I am just struggling with getting my head around some of the expressions and how they need to be laid out etc. 

I am currently trying work out how many times somebody had over weighed a product and so far I have the sum and count distinct in place, however it is pulling everything through and i am just looking for the sum result that is >=26.

My expression so far is:

Count(Distinct(aggr(sum({<[Ignores HSE]={0},[Weighing Method]={"Tare-Weigh"}>}Weighed),[KeyID])))

 I know its probably really simple but everything i am trying doesn't seem to work. 

Could somebody please point me in the right direction.

Thanks 

Lewis

Labels (1)
1 Solution

Accepted Solutions
Partner
Partner

aggr(if(sum(Weight)>=26,sum(Weight)),KeyID)

 

Capture.PNG

View solution in original post

8 Replies
MVP
MVP

Try to share a sample app/data and explain the expected output. That would give us a better picture about what you are after and could help better.

Contributor
Contributor

Morning Tresesco,

Please see below some data, I would like it to filter out all of the weighed results that come back >=26. I will then use this result to add dimensions such as name of colleague, product, Ingredient, dates etc. 

  
  
KeyIDWeight
411979105129.385
411979166428.256
411979216828.813
411979246428.81
411979278926.543
411980244730.364
412024230328.64
412024276428.82
Partner
Partner

Hi Lewis !

I changed your data a little bit to have only 5 (out of  8 ) that have weight >=26

 

1) To count how many have weight >=26 :

count(distinct {<Weight={"=sum(Weight)>=26"}>} KeyID)

2) to know the list of keys with weight >= 26 : 

Concat(distinct {<Weight={"=sum(Weight)>=26"}>} KeyID,', ')

3) to create a new dimension that only includes our 5 keys with weight>=26 :

=aggr(only(distinct {<Weight={"=sum(Weight)>=26"}>} KeyID),KeyID)

Capture.PNG

 

Hope this will help

MVP
MVP

Just tagging @Lewis_ward1  to have look at the solution proposed by @OmarBenSalem 

Contributor
Contributor

Hi @OmarBenSalem,

I have go it part way there but i'm now stuck again. 

So to explain a bit better:

I am trying to get the weight of the buckets which hold ingredients.

The data supplied to me has an Order ID, Batch ID and Bucket ID. As the bucket can be used more than once and could come back round within the same order i have CONCAT both the Batch ID and Bucket ID to make a KeyID. Each row in the data is by ingredient and amount so the KeyID is duplicated. 

I now need to sum all of the duplicate KeyID buckets with the Weighed column to get a total weight per KeyID

I will then need to only see buckets that are greater than or equal to 26.

Apologies I have only worked with Qlik in the last week and this is my first go at it alone project 😯

Partner
Partner

Difficult to understand .. I'll suggest you put your words in a table as preiviously shown with some samples and the wanted outcome.. would be much easier to understand and then/thus help !

Contributor
Contributor

Hi @OmarBenSalem ,

Please see below:

Raw Data

Order IDProductBatchBucket IDWeight
43779Ingrendient 141208212210
43779Ingrendient 24120827125
43779Ingrendient 341208261720
43779Ingrendient 141208312225
43779Ingrendient 444128311116
48729Spice141208261721
48729Spice147812912212
48729Spice241208261733
66321Water41208271214
66321Vinegar32584432912
18789Flour32875222227

 

Loaded into Qlik

Order IDProductKeyIDWeight
43779Ingredient 141208212210
43779Ingredient 24120827125
43779Ingredient 341208261720
43779Ingredient 141208312225
43779Ingredient 444128311116
48729Spice141208261721
48729Spice147812912212
48729Spice241208261733
66321Water41208271214
66321Vinegar32584432912
18789Flour32875222227

 

Initial sum

KeyIDWeight
32584432912
32875222227
41208212210
41208261774
41208271219
41208312225
44128311116
47812912212

 

Finial output needed >=26

KeyIDWeight
32875222227
41208261774

 

Thank you

Partner
Partner

aggr(if(sum(Weight)>=26,sum(Weight)),KeyID)

 

Capture.PNG

View solution in original post