Non Natural Set in Set Analysis

[Anonymous]

From HIC's blog on Natural Sets:

To sum numbers that are not assigned to a customer:

Customer	Amount
A	1
B	2
	3

he used this expression Sum({1-1<Customer={'*'}>}Amount) to get the following result:

Customer   Sum(Sales) Sum({1-1<Customer={'*'}>}Amount)

A	1	0
B	2	0
	3	3

I understand Customer={'*'} excludes the Null Customer. but interested to know how the expression calculates the value '3' only for Null Customer and '0' for valid Customers. Wouldn't (1-1) apply to Null Customer? Why it is not applied? Can some one please explain the logic here.

Please do not copy paste from HIC blog.

[sunny_talwar]

From what I understand, the second 1 is just to ignore any selections.{1-$<Customer = {'*'}>} should work the same way without any selection, but will filter out based on another field selection (such as selection in Year or another field).

1 is the set which include null, but <Customer = {'*'}> only include non-nulls Does that make sense?

[Anonymous]

Sunny,

Thanks for replying. I analysed a further more and here is what i found. Please correct me if I am wrong:

Since we have 3 dimension values:

A

B

<Null>

For A, it takes the dimension value and subtracts from the dimension value which is A (which matches the modifier '*') and returns 0. Similar with B

Whereas for Null, it takes the dimension value which is 3 and tries to subtracts the  dimension value (but the modifier does not match because '*' omits null). So  0 is the result. Finally 3-0=0. I think it works in this way.

Again, this is my guess only

[sunny_talwar · Accepted solution]

Karthick -

AFAIK... Set analysis is not carrying out the true calculation. It is used to determine if a certain row should be included in the expression or not.

Lets look at the two set and determine if it should be included in the set or not.

Set1 - 1

Set2 - 1{<Customer = {'*'}>}

Row where Customer is A

Set1 includes it

Set2 includes it

Set1- Set2 would not include (remember the venn diagram)

Row where Customer is B

Set1 includes it

Set2 includes it

Set1- Set2 would not include

Row where Customer is Null

Set1 includes it

Set2 doesn't include it because it only include those customers which have a value

Set1 - Set2 will thus include it

[Anonymous]

Sorry Sunny I am not understanding your statement:

Row where Customer is B

Set1 includes it

Set2 includes it

Set1- Set2 would not include

Row where Customer is Null

Set1 includes it

Set2 doesn't include it because it only include those customers which have a value

Set1 - Set2 will thus include it

If possible, could you please explain a bit more

Non Natural Set in Set Analysis

[Anonymous]

Nevermind Sunny. I understand the concept now

[sunny_talwar]

Karthick -

I am glad you finally understand it. I am not sure if something I offered helped you understand, or if you read something somewhere else that helped you understand. If it is the latter case, then please provide information or a link for that location so that others can benefit from it as well.

Also, please consider closing this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny