# 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

## 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)
• ### Qlik Sense

1 Solution

Accepted Solutions
Partner

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

8 Replies
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

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.

 KeyID Weight 4119791051 29.385 4119791664 28.256 4119792168 28.813 4119792464 28.81 4119792789 26.543 4119802447 30.364 4120242303 28.64 4120242764 28.82
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)

Hope this will help

MVP

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

Contributor

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

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

Please see below:

Raw Data

 Order ID Product Batch Bucket ID Weight 43779 Ingrendient 1 412082 122 10 43779 Ingrendient 2 412082 712 5 43779 Ingrendient 3 412082 617 20 43779 Ingrendient 1 412083 122 25 43779 Ingrendient 4 441283 111 16 48729 Spice1 412082 617 21 48729 Spice1 478129 122 12 48729 Spice2 412082 617 33 66321 Water 412082 712 14 66321 Vinegar 325844 329 12 18789 Flour 328752 222 27

Loaded into Qlik

 Order ID Product KeyID Weight 43779 Ingredient 1 412082122 10 43779 Ingredient 2 412082712 5 43779 Ingredient 3 412082617 20 43779 Ingredient 1 412083122 25 43779 Ingredient 4 441283111 16 48729 Spice1 412082617 21 48729 Spice1 478129122 12 48729 Spice2 412082617 33 66321 Water 412082712 14 66321 Vinegar 325844329 12 18789 Flour 328752222 27

Initial sum

 KeyID Weight 325844329 12 328752222 27 412082122 10 412082617 74 412082712 19 412083122 25 441283111 16 478129122 12

Finial output needed >=26

 KeyID Weight 328752222 27 412082617 74

Thank you

Partner

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

Tags