Add Measure with multiple if statements and an aggregation

[qlicky]

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. 🙂

[oskartoivonen]

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)