Count Distinct if sum result is greater than X

[Lewis_ward1]

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

[tresesco]

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.

[Lewis_ward1]

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

[OmarBenSalem]

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

[tresesco]

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

[Lewis_ward1]

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 😯

[OmarBenSalem]

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 !

[Lewis_ward1]

Hi @OmarBenSalem ,

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

[OmarBenSalem · Accepted solution]

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