Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Morning Everyone, I have a problem in one pie graph where I have one set analysis expression to calculate a distribution by a given variable.
With this expression I create my first distribution where Status=1
count({<Rc={'D','K'}, Status={1}>} ID)
Here is the problem, with the following expression, I want to create a distribution with Status<>1, but sometimes Status could be Null. Then the expression does not consider this value, and I do not have all the registers.
count({<Rc={'D','K'}, Status-={1}>} ID)
Thanks for your help.
There must be a easier way to do it, but since I don't know the easier way I can suggest you a work around.
In the script Create another field name called Status1 (still keep Status)
LOAD Status,
If(IsNull(Status), 0, Status) as Status1
FROM ...
and then use this new field name in your expression:
Count({<Rc={'D','K'}, Status1-={1}>} ID) (Never used it like this)
Count({<Rc={'D','K'}, Status1= e({<Status1 = {1}>})>} ID) (This is how I exclude things)
If 0 is already a Status value, consider replacing it with something which Status won't take to differentiate it.
Hope it helps.
Best,
S
Have a look here: http://community.qlik.com/search.jspa?q=include+null+in+set+analysis
- Marcus
Count({<Rc={'D','K'}, ID=-p({<Status1={1}>}) >} ID)
This looks nice. Do you know if he can use e instead of -p??
Count({<Rc={'D','K'}, ID= e({<Status1={1}>}) >} ID)
Just wondering.
Best,
S
I guess It could work too... The most important thing to consider is that we need to exclude ID's that have status=1, not status=1.
Let Leonardo try both and answer to us to know!!
Leo, podrás probar si sirven las dos?
Make sense. I thought that if I basically Status1 = e({<Status1 = {1}>} it would exclude the those ID's where Status1 = 1. But I guess I will have to give it a test run.
Thanks for your response sebastiandpereira
Best,
S
The Null values in Status "Doen't exists". For example, supossing that we have Status=1, and Status=2, and some null values:
ID Status
A 1
B 2
C
With two list boxes with ID and Status fields. If you select value 1 in status, the available value in ID will be A. So, you click on "Select excluded values" in Status field. Now you will select Status=B, and the available ID will be B, not B and C.
If you select status=1, and select excluded values in ID field, you will give what you need.
The SET Analysis is similar to make selections. When you exclude Status values, you are selecting the rest, and if you are selecting the rest, you are excluding the nulls. But, if you exclude ID's with some Status values, the null is the "exclusion" of the "exclusion".... sorry, my english is not good, I'm being very confuse!!
I recommend you to try this kind of exercises when you have to do complex SA expressions.
Thanks for going over this in detail. I will definitely going to give it a try.
Best,
S
Thanks for your idea, I resolved the problem using an If sentence in the script, I assigned the value that in a new variable, then in the set analysis I apply the filter using this one.