topic Re: Conditional sum / set analysis in QlikView

Conditional sum / set analysis

Anonymous — Thu, 13 Jun 2013 15:08:25 GMT

Hello,

I have the following data table:

A	B	C
Name 1	B1	10
Name 1	B2	20
Name 2	B3	30
Name 2	B4	40

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

Re: Conditional sum / set analysis

shree909 — Thu, 13 Jun 2013 15:14:36 GMT

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

Re: Conditional sum / set analysis

Anonymous — Thu, 13 Jun 2013 15:14:45 GMT

It should be something in this direction:

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

Regards,

Michael

Re: Conditional sum / set analysis

Thu, 13 Jun 2013 15:17:38 GMT

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

Regards.

Re: Conditional sum / set analysis

Anonymous — Thu, 13 Jun 2013 15:17:51 GMT

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!

Re: Conditional sum / set analysis

Anonymous — Thu, 13 Jun 2013 15:29:29 GMT

Thanks Michael.

Indeed that is the way to go and it worked.

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

Re: Conditional sum / set analysis

Anonymous — Thu, 13 Jun 2013 15:31:10 GMT

Nice 🙂

How I forgot about E? 😞

Re: Conditional sum / set analysis

Anonymous — Thu, 13 Jun 2013 15:43:04 GMT

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

Re: Conditional sum / set analysis

Anonymous — Fri, 14 Jun 2013 08:53:09 GMT

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?

Re: Conditional sum / set analysis

Anonymous — Fri, 14 Jun 2013 12:35:37 GMT

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)

Re: Conditional sum / set analysis

Anonymous — Fri, 14 Jun 2013 13:22:21 GMT

Thanks Micheal.

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

This part is getting underlined with a red line.and the chr() function are not activated.

I managed to add parenthesis to it a bit in order to activate them but still get a red underline in here {:

I spaced the expression to delimit the red underline

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

Re: Conditional sum / set analysis

Anonymous — Fri, 14 Jun 2013 13:27:29 GMT

Ignore the red underline, it is not always indicates an error. Try this in a text box - you see red but it works:

chr(39)&concat(distinct B & chr(39) & ',' & chr(39)) & chr(39)

Re: Conditional sum / set analysis

Anonymous — Fri, 14 Jun 2013 13:31:18 GMT

I tried ignoring it, but I wouldnt get any form of result

Re: Conditional sum / set analysis

Anonymous — Fri, 14 Jun 2013 13:33:05 GMT

Maybe I mistyped something. If you can upload a sample of your application, I'll take a look.

Re: Conditional sum / set analysis

Anonymous — Fri, 14 Jun 2013 14:02:56 GMT

Thanks Michael.

Here you go.

Kind regards,

Re: Conditional sum / set analysis

Anonymous — Fri, 14 Jun 2013 14:10:24 GMT

Here it goes:

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

Re: Conditional sum / set analysis

Anonymous — Fri, 14 Jun 2013 14:17:39 GMT

Michael,

it is behaving as sum(C) would.

Whereas what I am trying to achieve is the following:

If I select B1, then the output must exclude all A values that have B1.

Re: Conditional sum / set analysis

Anonymous — Fri, 14 Jun 2013 14:35:57 GMT

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

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

Re: Conditional sum / set analysis

Anonymous — Fri, 14 Jun 2013 14:59:24 GMT

Thank you very much!