topic Re: Error in Calculation in Set Analysis Expression of a Pie Graph in QlikView

Error in Calculation in Set Analysis Expression of a Pie Graph

Thu, 29 Jan 2015 13:31:29 GMT

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.

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

sunny_talwar — Thu, 29 Jan 2015 13:40:59 GMT

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,

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

marcus_sommer — Thu, 29 Jan 2015 13:53:23 GMT

Have a look here: http://community.qlik.com/search.jspa?q=include+null+in+set+analysis

- Marcus

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

sebastiandperei — Thu, 29 Jan 2015 14:27:22 GMT

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

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

sunny_talwar — Thu, 29 Jan 2015 14:38:13 GMT

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,

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

sebastiandperei — Thu, 29 Jan 2015 15:39:11 GMT

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?

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

sunny_talwar — Thu, 29 Jan 2015 15:48:51 GMT

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,

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

sebastiandperei — Thu, 29 Jan 2015 17:31:02 GMT

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

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.

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

sunny_talwar — Thu, 29 Jan 2015 17:52:25 GMT

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

Best,

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

Thu, 29 Jan 2015 20:30:32 GMT

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.

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

sunny_talwar — Thu, 29 Jan 2015 20:43:49 GMT

Awesome. As long as you were able to sort it out, its great.

Best,

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

Anonymous — Thu, 29 Jan 2015 23:12:00 GMT

Ciao Friend- try this expression =count({<RC={'D','K'}, Status={"*"}>-<RC={'D','K'}, Status={1}>} ID).

Hope it helps.

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

Fri, 30 Jan 2015 14:43:41 GMT

Thank you Friend, I tried that sentence but it did not throw me the same result as the script solution.

Regards

Re: Error in Calculation in Set Analysis Expression of a Pie Graph

Anonymous — Fri, 30 Jan 2015 15:59:49 GMT

Thanks for your reply. I have basically created a set that has all the data excluding null values and the another set which does not have values for status=1. Then I have subtracted second set from the first set that exclude null as well as 1 from the first set. I guess that’s what probably you wanted. Attached is the sample qvw and data set. Let me know if you wanna send your app, I can check what’s going wrong there.