topic Re: Exclude in Set Analysis in QlikView

Exclude in Set Analysis

richard_chilvers — Mon, 20 Apr 2015 09:07:37 GMT

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)

Re: Exclude in Set Analysis

alexandros17 — Mon, 20 Apr 2015 09:08:28 GMT

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

let me know

Re: Exclude in Set Analysis

hari8088 — Mon, 20 Apr 2015 09:26:24 GMT

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

try it.

Re: Exclude in Set Analysis

Mon, 20 Apr 2015 09:49:25 GMT

Hi ,

Looks like u formula is correct it has to work ,

try these

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

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

Re: Exclude in Set Analysis

Mon, 20 Apr 2015 09:53:30 GMT

Hi Richard,

You can try

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

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

Regards

Deepak

Re: Exclude in Set Analysis

satishkurra — Mon, 20 Apr 2015 11:15:57 GMT

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

Re: Exclude in Set Analysis

richard_chilvers — Mon, 20 Apr 2015 12:57:36 GMT

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