Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Optimising a formula from IF to Set Analysis

Hi All,

How can I optimise the following formula ? (it is slow on a large data set)

Sum(If(GLTranType = 'OB',If(GLTranDate = vMinDate,GLTranAmountOB / C_Rate),GLTranAmountOB / C_Rate))

As you can see, I only want the 'OB' GLTranType where the date of the transaction is equal to the minimum selected date.

5 Replies
Miguel_Angel_Baeyens

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.

johnw
Champion III
Champion III

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 non-intersecting 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.

Not applicable
Author

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?

johnw
Champion III
Champion III

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)

Not applicable
Author

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