Add Measure with multiple if statements and an aggregation

qlicky — Tue, 08 Nov 2022 06:13:23 GMT

Hi,

i need to implement 3 measures in a table which are to complex for me to handle as a QLIK beginner.

Given the following table

id	check	min	max	value	name	case 1	case 2	case 3
						2	2	1
1	1	1	3	2	A	0	0	0
1	2	1	3	0	A	1	0	0
1	5	1	3	4	A	1	0	0
2	6	0	1	1	A	0	0	0
2	-2	0	1	5	A	0	1	0
3	4	2	3	2	A	0	0	0
3	-2	2	3	3	A	0	1	1
3	1	2	4	0	A	1	0	1
4	-1	2	4	4	B	0	0	0
4	1	2	4	2	A	0	0	0

i need to get a count/sum if the following conditions are met.

case 1: get the amount of id, which have at least one line with min<=value<=max andcheck > 0
case 2: get the amount of id, which have at least one line with value>0 and check < 0 and name = "A"
case 3: get the amount of id, for which both conditions case 1 and case 2 are met

What i came up with for case2 so far as an example is:

Sum(Aggr(If(check<0 and value>0 and name='A' ,1,0), id))

Unfortunately this doesn't work because it messes up the aggregation. The pure if-statement

If(check<0 and value>0 and name='A' ,1,0)

seems to work as it provides the correct cell values (0/1) for "case2" for each line.

Any help is appreciated. 🙂

Re: Add Measure with multiple if statements and an aggregation

oskartoivonen — Thu, 17 Nov 2022 21:48:26 GMT

You would most likely benefit a lot from reading up on set analysis in Qlik, it's the correct approach to building your desired expressions.

Case1:

Count({< value = {">=Min<=Max"}, check = {">0"} >} DISTINCT id)

Case2:

Count({< value = {">0"}, check = {"<0"}, name = {'A'} >} DISTINCT id)

Case3 requires an intersection of two different sets, but unfortunately, the check field can never be both higher and lower than zero at the same time, so no id should ever satisfy that requirement.:

Count({< value = {">=Min<=Max"}, check = {">0"} >} * {< value = {">0"}, check = {"<0"}, name = {'A'} >} DISTINCT id)

topic Add Measure with multiple if statements and an aggregation in App Development

Add Measure with multiple if statements and an aggregation

Re: Add Measure with multiple if statements and an aggregation