Sum values of a column based on meeting 2 other criteria (Similar to Sumifs in excel)

[JustinM]

Hi

I'm brand new to Qlik Sense so still finding my way around.

I am trying to create a Master Item that will calculate the sum of another measure based on meeting certain criteria. 

Example:

In the following Table I would like to sum the values in a value column based on an ID and where the Months are the same . It will then give the Expected Answer.

I managed to get one part right to sum the value based on ID

Sum(TOTAL <id> value)

but do not know how to add the criteria for Month?

Month	ID	Type	Value	Expected Answer
202001	1	2	10	30
202001	1	4	20	0
202001	1	2	20	30
202001	1	4	30	0
202002	1	2	40	60
202002	1	4	20	0
202002	1	2	20	60
202001	2	2	10	60
202001	2	2	20	60
202001	2	2	30	60
202001	3	2	20	50
202001	3	2	30	50
202001	3	4	30	0

Do not have programming back ground so would appreciate reason for each part of solution if that is not too much to ask

Thanks

[morgankejerhag]

Welcome to Qlik! Are the columns 1-4 in the data? Are you showing this in a table? Which dimensions do you use? If you make a table with Month and ID as dimensions and the measure sum(Value) you might get what you are looking for.

Morgan Kejerhag
Senior Business Intelligence Consultant
Drake Analytics
http://www.drakeanalytics.se

[JustinM]

Hi Morgan

Thanks for the reply

Yes, columns 1-4 is in the data. I want to use the results of the above query as part of a measure tin a pivot table hat would distinct count the id, where the sum was larger than 0?

[Saravanan_Desingh]

Are you sure202001,1,4 is 0? It should be 50(20+30) right?

[morgankejerhag]

Which dimensions do you have in the pivot?

Morgan Kejerhag
Senior Business Intelligence Consultant
Drake Analytics
http://www.drakeanalytics.se

[JustinM · Accepted solution]

Hi

Thank you for the assistance. I've found the way to fix my query:

Sum(TOTAL <id,Month>Value)

and it looks like the rest of my problem needs some sort of Set parameter solution, so once I figure out how to approach that I will resubmit a question.

Much appreciated