topic Set Analysis using an If in QlikView

Set Analysis using an If

cliff_clayman — Tue, 06 Dec 2016 22:11:19 GMT

I am trying to use the following set analysis expression:

Sum(If({$<Division={'B'},FY={$(vFY)}>}+,+))

What I am trying to do is get the Sum of + when Division = 'B' and FY = vFY and if it does not, then get the Sum of + . The current expression is not valid. How can I write this?

Re: Set Analysis using an If

sunny_talwar — Tue, 06 Dec 2016 22:17:31 GMT

May be like this:

Sum(If(Division='B' and FY=$(vFY), + , +))

Re: Set Analysis using an If

sunny_talwar — Tue, 06 Dec 2016 22:19:10 GMT

Or this:

RangeSum(Sum({$<Division*= {'B'}, FY *= {$(vFY)}>} +), Sum({$<Division -= {'B'}, FY -= {$(vFY)}>} +))

Re: Set Analysis using an If

cliff_clayman — Tue, 06 Dec 2016 22:19:34 GMT

Can I not use Set Analysis for this type of expression?

Re: Set Analysis using an If

sunny_talwar — Tue, 06 Dec 2016 22:22:59 GMT

You might, but not every thing can be solved using if statement.

Re: Set Analysis using an If

johnw — Wed, 07 Dec 2016 00:17:07 GMT

The second sum looks incorrect to me. Say vFY = 2016, and we have this data:

FY Division
2015 A
2016 A
2015 B
2016 B

The first sum will correctly get row 4. The second sum is intended to get rows 1, 2, and 3, but I think it will only get row 1. And I don't think we need rangesum() because sum() should never return null, I believe.

I think this would do the trick.

sum({<Division={'B'},FY={'$(vFY)'}>} A+B)+sum({$-<Division={'B'},FY={'$(vFY)'}>} C+D)

Re: Set Analysis using an If

sunny_talwar — Wed, 07 Dec 2016 00:58:31 GMT

Yes sir, I forgot my Venn Diagram lessons here . This should work I believe

RangeSum(Sum({$<Division*= {'B'}, FY *= {$(vFY)}>} +), Sum({$<Division -= {'B'}>+<FY -= {$(vFY)}>} +))

Updated from * to + based on John's response

Re: Set Analysis using an If

johnw — Wed, 07 Dec 2016 02:27:48 GMT

Almost, but same problem:

<Division-={'B'}> = {1,2}
<FY-={$(vFY)}> = {1,3}

{1,2}*{1,3} = {1}

{1,2}+{1,3} = {1,2,3}

So we need to use union + instead of intersection *, but otherwise, yes, you can fix your expression as you showed. I think mine's a little more clear, as it has the if/else form of "some set" and "everything except that set", but both look like they'd work.

Re: Set Analysis using an If

sunny_talwar — Wed, 07 Dec 2016 02:29:11 GMT

Hahahaha yes.... how can I be so stupid... Well at time I can be. My intentions were right, my expressions were not