4 Replies Latest reply: Sep 16, 2011 8:23 AM by Jason Michaelides RSS

    Expression Issue

      If anyone else here has tried converting a heavy formula crystal report, they know it can be a pain.

       

      I have a formula in crystal that access's other formulas to get data. With qlikview I had to combine all formulas into one expression, and then also incorporate set analysis to get around some other issues with possible selections vs crystal's parameter options.

       

      The result is the folowing formula. However this expression is not returning any data. The pivot that it resides in just shows blank.

      The expression is complicated enough, and does not show any errors so I have no idea what is going on.

      Can anyone with more experience help me out?

       

      if( left(glaccount,1)='1', 
      //{@current}
      (sum({$< [Fiscal Period] = {"<=$(=Max([Fiscal Period]))"}>} if([Category Description]<>'Cash & Equivalents',(if( [Fiscal Period]=0  , 0 ,  creditamt+debitamt)  + openbalance))*-1))
      //end current
       +  
      //{@Prior}
      (if (Max([Fiscal Period])-Min([Fiscal Period])<>1, 
      (sum({$< [Fiscal Period] = {"<$(=Max([Fiscal Period]))"}>}if([Category Description]<>'Cash & Equivalents',  
      if ([Fiscal Period]=0  , 0 , (creditamt+debitamt)  + openbalance),
      sum({$< [Fiscal Period] = {"<=$(=Max([Fiscal Period]))"}>}if([Category Description]<>'Cash & Equivalents',  
      if ([Fiscal Period]=0  , 0 , (creditamt+debitamt)  + openbalance),0)
      ))))))
      //end prior
      ,
      //Current
      (sum({$< [Fiscal Period] = {"<=$(=Max([Fiscal Period]))"}>} if([Category Description]<>'Cash & Equivalents',(if( [Fiscal Period]=0  , 0 ,  creditamt+debitamt)  + openbalance))*-1))
      //end current
      -
      //Prior
      (if (Max([Fiscal Period])-Min([Fiscal Period])<>1, 
      (sum({$< [Fiscal Period] = {"<$(=Max([Fiscal Period]))"}>}if([Category Description]<>'Cash & Equivalents',  
      if ([Fiscal Period]=0  , 0 , (creditamt+debitamt)  + openbalance),
      sum({$< [Fiscal Period] = {"<=$(=Max([Fiscal Period]))"}>}if([Category Description]<>'Cash & Equivalents',if([Fiscal Period]=0  , 0 , (creditamt+debitamt)  + openbalance),0)
      ))))))
      //end prior
      )
      

       

      Edit: Here are the Crystal formulas if that helps at all:

       

      Main Formula:

      if {@Group 1 name}="Assets" then ((Sum ({@current}, {coaactcat.number01})*-1) + Sum ({@Prior}, {coaactcat.number01})) else

      (Sum ({@current}, {coaactcat.number01})*-1)-(Sum ({@Prior}, {coaactcat.number01})*-1)

       

      @current:

      if {glperiodbal.fiscalperiod}<={?Fiscal Period to}and {coaactcat.description}<>"Cash & Equivalents" then {@Combined} else 0

       

      @prior:

      if {?Fiscal Period to}-{?Fiscal Period From:}<>1 then

      (if {glperiodbal.fiscalperiod}<({?Fiscal Period from:})and {coaactcat.description}<>"Cash & Equivalents" then {@Combined 3} else 0)

      else(if {glperiodbal.fiscalperiod}<=({?Fiscal Period from:})and {coaactcat.description}<>"Cash & Equivalents" then {@Combined 3} else 0)

       

      @combined:

      (if {glperiodbal.fiscalperiod}=0  then 0 else  {glperiodbal.creditamt}+{glperiodbal.debitamt})  + {glperiodbal.openbalance}

       

      @combined3:

      (if {glperiodbal.fiscalperiod}=0  then 0 else  {glperiodbal.creditamt}+{glperiodbal.debitamt})  + {glperiodbal.openbalance}

       

      {?Fiscal Period to}: Min fiscal period selected

       

      {?Fiscal Period from:}: Max fiscal period selected

        • Expression Issue
          Jason Michaelides

          Wow - big expression...!

           

          I've not looked at it in detail but the way I would work with something of this size is to put all the component parts into separate variables and see if each of them is returning the right value on its own. Then build the final expression using the evaluated variables.  Helps to see where it might be going wrong - in a component part or the final construction.

           

          Try that and see how it works out. Using variables also means if you ever need to change the expression you only do it in one place.

           

          Hope this helps,

           

          Jason

            • Expression Issue

              Jason,

               

              Yeah I was thinking I could do that after I posted. This is just one of the examples that I find it a pain in trying to transfer crystal logic over to Qlikview, this and trying to convert running totals/crystal variable logic.

               

              I will try breaking it apart into the seperate variables to see if that works.

              • Re: Expression Issue

                Separating them is what helped. It allowed me to see that when I separated my expression for Prior, that I had the parenthesis wrong.

                 

                 

                (if(Max([Fiscal Period])-Min([Fiscal Period])<>1, 
                sum({$< [Fiscal Period] = {"<$(=Max([Fiscal Period]))"}>}if([Category Description]<>'Cash & Equivalents',  
                if ([Fiscal Period]=0  , 0 , (creditamt+debitamt)  + openbalance))),
                sum({$< [Fiscal Period] = {"<=$(=Max([Fiscal Period]))"}>}if([Category Description]<>'Cash & Equivalents',if([Fiscal Period]=0  , 0 , (creditamt+debitamt)  + openbalance),0)
                )))
                

                 

                 

                This is the correct expression. The total expression is now at least giving me a result, even if it is the wrong result. I at least have something to work with now.

                 

                Thank you.