Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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. 🙂
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)