
Optimising a formula from IF to Set Analysis
Miguel Angel Baeyens de Arce Jun 15, 2010 3:18 AM (in response to Matt Sweeney )Hello Matt,
Here is what I would do, since Set Analysis does not support else statements:
If(GLTranType = 'OB', Sum({< GLTranType = {'OB'}, GLTranDate = {'$(vMinDate)'} >} GLTranAmountOB / C_Rate), Sum(GLTranAmountOB / C_Rate)
Hope that helps.

Optimising a formula from IF to Set Analysis
John Witherspoon Jun 15, 2010 7:50 PM (in response to Miguel Angel Baeyens de Arce )True, set analysis doesn't support ELSE, but it does support unions of sets, which sould be enough. First note that we can rewrite the original expression like this:
sum(if((GLTranType = 'OB' and GLTranDate = vMinDate) or GLTranType <> 'OB',GL_TranAmountOB / C_Rate))
That removes our ELSE and turns it into an OR. Then it's just a matter of translating to set analysis syntax. Since the IF takes a subset of the selections, we won't be using a straight = in our set analysis, but rather a *=, which says to intersect with our current selections (giving us the desired subset). For the not equal 'OB', we can handle this with a =, which says to remove 'OB' from the current selections. Finally, the OR is handled with a union of two nonintersecting sets, <set1>+<set2>. Put it all together, and I believe you get this (assuming vMinDate is in the same exact date format as GLTranDate):
sum({<GLTranType*={'OB'},GLTranDate*={'$(vMinDate)'}>+<GLTranType={'OB'}>} GLTranAmountOB/C_Rate)
Edit: Wait, I think it's even simpler as long as we recognize that we don't need a literal translation of the IF, but only need the same result. I believe these will both give us the exact same result. In this case, set union and exclusion turn out to be simpler than AND and OR. Oh, and I didn't need the *= on the date, because presumably the vMinDate is already set from the selections.
sum({$<GLTranType={'OB'},GLTranDate={'$(vMinDate)'}>} GLTranAmountOB/C_Rate)
sum({<GLTranType={'OB'}>+<GLTranDate={'$(vMinDate)'}>} GLTranAmountOB/C_Rate)
Edit2: I'm betting the first expression of the two above is faster. Both use two sets, but one uses $ as one of the two sets. QlikView has already built the $ set, so I suspect it only needs to build one set to satisfy that expression, but two to satisfy the second. Not sure how fast it is at exclusion vs. union, though, which could make a difference as well.

Optimising a formula from IF to Set Analysis
Matt Sweeney Jun 16, 2010 1:00 AM (in response to John Witherspoon )John, thanks for your feedback.
I've tested each of your suggested formulas and they don't seem to evaluate where there is a null group in the pivot table.
You can see this below;
Edit: Your first suggested formula is the far RHS column  the second from the right is the original formula (correct results)
I can't think of why this is occuring. Any ideas?

Optimising a formula from IF to Set Analysis
John Witherspoon Jun 16, 2010 2:57 PM (in response to Matt Sweeney )Ah, looks like IF and set analysis respond to nulls differently. Set analysis is building a set of data, just like you were making selections. So when it selects a set like <GLTranType={'OB'}>, it's selecting all values that are not 'OB'. But null isn't a value, so it isn't part of the set any more than it would be if you were doing selections.
However, it looks like you can replace <field={'value'}> with $<field={'value'}> to pick up the nulls, and it looks like you can create compound set expressions with parenthesis. So I'm thinking this might do the trick?
sum({($<GLTranType={'OB'}>)+<GLTranDate={'$(vMinDate)'}>} GLTranAmountOB/C_Rate)

Re: Optimising a formula from IF to Set Analysis
Oneview Support Oct 3, 2013 10:42 AM (in response to John Witherspoon )I have
Load * inline [
Year, Count
2005, 4
2006, 6
2007, 8 ];
In chart I have writen
=if(Year='2005',sum({1}Count),sum({1}Count)/2)
Can I change the above if statement into Set Analysis



