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

    YTD Calc using SelectedYearVariable



      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,