Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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?
Are you sure202001,1,4 is 0? It should be 50(20+30) right?
Which dimensions do you have in the pivot?
Morgan Kejerhag
Senior Business Intelligence Consultant
Drake Analytics
http://www.drakeanalytics.se
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