Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
cliff_clayman
Contributor

Set Analysis using an If

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 expre...

8 Replies

Re: Set Analysis using an If

May be like this:

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

Re: Set Analysis using an If

Or this:

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

cliff_clayman
Contributor

Re: Set Analysis using an If

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

Re: Set Analysis using an If

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

MVP
MVP

Re: Set Analysis using an If

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)

Highlighted

Re: Set Analysis using an If

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

MVP
MVP

Re: Set Analysis using an If

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

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

Community Browser