Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
Partner
Partner

Conditional sum / set analysis

Hello,

I have the following data table:

ABC
Name 1B110
Name 1B220
Name 2B330
Name 2B440

I want to build a chart that can tell me the sum of the names have no B2.

Note that I tried using the following set analysis:

sum({$<B={"*"}-{"B2"}>} C)

However, instead of getting 70 (30+40), I am obtaining 80 (30+40+10) which is logical.

How can I make the expression "behave" so that I can exclude any trace of Name 1 (since one of its occurences contains B2)

Any ideas would be of great value.

Cheers

1 Solution

Accepted Solutions
Highlighted
Champion III
Champion III

Re: Conditional sum / set analysis

Sure...  We select B now, hence affecting A.  See attached.

The most important change here is replacing {$} with {1}

View solution in original post

18 Replies
Highlighted
Partner
Partner

Re: Conditional sum / set analysis

Hi

IF i understood clearly u want to exclude all the time Field A with value Name1

ucan use this

sum({$<B={"*"}-{"B2"},A-={'Name 1'}>} C)

Thanks

Highlighted
Champion III
Champion III

Re: Conditional sum / set analysis

It should be something in this direction:

sum({<A=P({1<B-={'B2'}>})>} C)

Regards,

Michael

Highlighted
Not applicable

Re: Conditional sum / set analysis

Did you try with if( B like '*B2*' ,sum(C))?

Regards.

Highlighted
Partner
Partner

Re: Conditional sum / set analysis

If I do this, then if changes occur to name 1 by which it doesn't have B2, then I will exclude relevant data.

But thank you for the lead!

Highlighted
Partner
Partner

Re: Conditional sum / set analysis

Thanks Michael.

Indeed that is the way to go and it worked.

sum( {$<A = E({1<B={‘B2’}>})>} C )

Highlighted
Champion III
Champion III

Re: Conditional sum / set analysis

Nice 🙂

How I forgot about E?  😞

Highlighted
Partner
Partner

Re: Conditional sum / set analysis

Well in your defense, up to until 5 minutes ago, I had no clue that the function even existed.

And I can sleep happier today

Highlighted
Partner
Partner

Re: Conditional sum / set analysis

It gets trickier.

Let's say that I want to be able to add a parameter instead of the fixed B2 value.

I use then getfieldselections(B) in order to have a variable input

I read that the appropriate syntax would be:

sum( {$<A = E({1<B={"$(=getfieldselections(B)"}>})>} C )

But for some obscure reason, it doesn't work.

a - Is there a known limitation with E() and P() functions by which a function cannot be an argument of the expression?

b - Do I have an error in my syntax?

Highlighted
Champion III
Champion III

Re: Conditional sum / set analysis

the problem is that getfieldselections returns comm-separated values, but you need comm-separated values enclosed in single quotation marks.  If you always have something selected in this field, you can use this:

sum( {$<A = E({1<B={chr (39) & concat(distinct B, chr(39) & ',' & chr(39)) & chr(39) }>})>} C)

If there are no selections in field B, the list includes all values, son if it is not desirable, just add a condition that getselectedcount(B)>0.

Regards

Michael

Edit: fixing expression: sum( {$<A = E({1<B={"$(=chr (39) & concat(distinct B, chr(39) & ',' & chr(39)) & chr(39) ) "}>})>} C)