Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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.

Tags (3)
13 Replies
Highlighted

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

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Specialist
Specialist

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

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

Highlighted

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

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

Highlighted
Specialist
Specialist

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

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?  

Highlighted

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

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

Highlighted
Specialist
Specialist

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

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.

Highlighted

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

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

Best,

S

Highlighted
Not applicable

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

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.