5 Replies Latest reply: Oct 3, 2013 10:42 AM by Oneview Support RSS

    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.

        • Optimising a formula from IF to Set Analysis
          Miguel Angel Baeyens de Arce

          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

              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.

                • Optimising a formula from IF to Set Analysis

                  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

                      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)