Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Error in Calculation in Set Analysis Expression of a Pie Graph

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.

13 Replies
sunny_talwar

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

marcus_sommer

sebastiandperei
Specialist
Specialist

Count({<Rc={'D','K'}, ID=-p({<Status1={1}>}) >} ID)

sunny_talwar

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

sebastiandperei
Specialist
Specialist

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?  

sunny_talwar

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

sebastiandperei
Specialist
Specialist

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.

sunny_talwar

Thanks for going over this in detail. I will definitely going to give it a try.

Best,

S

Not applicable
Author

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.