Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
richard_chilvers
Specialist
Specialist

Exclude in Set Analysis

I have this simple expression to calculate a value when there is a D in the field called DrinksExtra.

sum( {$<DrinksExtra={'D'}>} Drinks_Value)

I now want the calculation when there is NOT a D in the field. I tried the following but it doesn't work.

sum( {$<DrinksExtra-={'D'}>} Drinks_Value)

6 Replies
alexandros17
Partner - Champion III
Partner - Champion III

sum( {$<DrinksExtra={'*'}-{'D'}>} Drinks_Value)

let me know

hari8088
Creator
Creator

sum( {$<DrinksExtra=e{'D'}>} Drinks_Value)

try it.

Not applicable

Hi ,

Looks like u formula is correct it has to work ,

try these

=Sum({$<DrinksExtra-={"D"}>} Drinks_Value)

or

=Sum({$<DrinksExtra=E({"D"})>} Drinks_Value)

Not applicable

Hi Richard,

You can try

sum( {$<DrinksExtra=-{'D'}>} Drinks_Value)


or

sum( {$<DrinksExtra={'*'}-{'D'}>} Drinks_Value)



Regards

Deepak

satishkurra
Specialist II
Specialist II

Hi

Your expression is also syntactically correct. Please find below the analysis

Expression1:

sum( {$<DrinksExtra-={'D'}>} Drinks_Value)


Expression2:

sum( {$<DrinksExtra=-{'D'}>} Drinks_Value)


Both the expressions are fine. It depends upon the type of requirement you are achieving.


See here...


The difference is on which side of "=" we add the "-" sign. The first Set expression looks similar to the following

sum( {$<DrinksExtra=-{'D'}>} Drinks_Value)


This is actually syntactically the same as this (and this is a valid syntax):


Sum({<DrinksExtra={*}-{'D'}>} Drinks_Value)


{*} here means all possible values of the year.


This Set syntax will always override the selection on DrinksExtra, so it will not change when we choose

a different DrinksExtra. The result stays the same.

The second Set expression looks similar to the following:

Sum({<DrinksExtra-={'D'}>} Drinks_Value)

This is actually and "additive" (or, in this case, "subtractive") way of writing the Set syntax.

It is syntactically the same as follows:

Sum({<DrinksExtra=DrinksExtra-{'D'}>} Drinks_Value)

It is upto you what should be done while selecting the DrinksExtra list box....

Let me know if there is any queries...

Thanks

Satish


richard_chilvers
Specialist
Specialist
Author

Many thanks to everyone who took trouble to reply.

The general thread is that my syntax is OK.

So I have gone back to the data build within the script and suspect there is something wrong there.

Will revert if I can't track down the issue.

Sorry, I couldn't find the 'Helpful Answer' button