QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
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...

Tags (2)
8 Replies
MVP

Re: Set Analysis using an If

May be like this:

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

MVP

Re: Set Analysis using an If

Or this:

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

Contributor

Re: Set Analysis using an If

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

MVP

Re: Set Analysis using an If

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

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
MVP

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

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.

MVP

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