2 Replies Latest reply: May 16, 2012 7:48 AM by Marc Livingston RSS

    I need =P() for last year selection, not current year selection

      Current Year:

       

      (sum(if(wildmatch([GL Account],'5*','6*'),[Journal Debit Amount]))-sum(if(wildmatch([GL Account],'5*','6*'),[Journal Credit Amount])))
      /
      
      sum({1<[Top Income]={'Sales'},Company=P(),[Fiscal Period]=P(),[Fiscal Year]=P()>} ([Credit Amount]+[Debit Amount])* (-1))
      

       

       

       

      Previous Year:

       

       

      (sum({<[Fiscal Period]=P(),[Fiscal Year]={"$(=([Fiscal Year])-1)"}>}
      if(wildmatch([GL Account],'5*','6*'),[Journal Debit Amount]))-
      sum({<[Fiscal Period]=P(),[Fiscal Year]={"$(=GetFieldSelections([Fiscal Year])-1)"}>}
      if(wildmatch([GL Account],'5*','6*'),[Journal Credit Amount])))
      /
      
      sum({1<[Top Income]={'Sales'},Company=P(),[Fiscal Period]=P(),[Fiscal Year]={"$(=([Fiscal Year])-1)"}>} ([Credit Amount]+[Debit Amount])* (-1))
      

       

       

      I have the above 2 expressions that I am using to compare current year expense percentage of revenue vs last year expense percentage of revenue.

       

      First off, I have the fiscal year set to always 1 selected value. Currently I have 2012 selected. In 2012 there are 4 companies (with current selection of expense) available. Last year there was only one for current selections of expense.

       

      The above 2 formulas return the correct expense amounts for each year. However due to Company=P() part of last year revenue set I am pulling all 4 compaines saves vs the only 1 companies expenses. This is making the values different then they actually were.

       

      Is there anyway to tell the set to take the possible Companies for the same selection, but assuming it was last year and not this year?