7 Replies Latest reply: Aug 2, 2013 2:06 AM by Tresesco B RSS

    YTD Calc using SelectedYearVariable

      Hi,

       

      I need to calculate Net Income comparing Selected Fiscal YTD to Previous Fiscal YTD (ie. July 1-August 1, 2013 compared to July 1-August 1, 2012). I have a variable that drives which year is selected 'vBrokerageSelectedYear' which looks likes this:

       

      vBrokerageSelectedYear=if(IsNull (Fiscal_Year),Year(addmonths(Today(),6)),Fiscal_Year)

       

      I can't change the variable because it drives many other tabs.

       

      This is the expression I'm using to try and calculate the previous year's Net_Income based on Fiscal_Year and Invoice_Period_Field:

       

      =num(sum({<Fiscal_Year={"$(=vBrokerageSelectedYear-1)"},Fiscal_Year_Earned=,I_Month=,I_Month_Earned=, Invoice_Period_Field={"<=$(=Num(Date#(Day(today())&'-'&Month(Today())&'-'&(vBrokerageSelectedYear-1),'DD-MMM-YY'),0))"}>}Net_Income),'$#,##0')

       

      The expression returns the FULL Previous Fiscal Year, not YTD. I believe the problem lies somewhere in the Invoice_Period_Field part of the expression.

       

      Any assistance would be greatly appreciated.

       

      Thank you in advance,

      Matt