5 Replies Latest reply on Jul 17, 2012 11:08 AM by Jonathan Drummey

    passing calculated fields to another calculated field

    Ryan Pagila

      Is there a limitation to passing a calculated field into another calculated field?  Here is my example.

       

      First, I have two parameters called AsOfDate and TransactionPeriod, which a user selects.  The list values for TransactionPeriod are
      Day, Week, Month, Quarter, and Year.

       

      Step 1:

      I created a calculated field called LastYearPeriodEndDate

       

      IF [TransactionDate]= [AsOfDate] Then [SameDateLastYear] END

       

      Step 2:

      I created another calculated field to give me LastYearPeriodBeginDate using

       

       

      case [TransactionPeriod]

      when 'Day' then dateadd('day',0,[LastYearPeriodEndDate])

      when 'Week' then dateadd('day',1,dateadd('day',-7,[LastYearPeriodEndDate]))

      when 'Month' then dateadd('day',1,dateadd('month',-1,[LastYearPeriodEndDate]))

      when 'Quarter' then dateadd('day',1,dateadd('month',-3,[LastYearPeriodEndDate]))

      when 'Year' then dateadd('day',1,dateadd('month',-12,[LastYearPeriodEndDate]))

      end

       

      Step 3:

      I then pass LastYearPeriodEndDate and LastYearPeriodBeginDate into another calculated field to get me LastYearPeriodRange

       

      if [TransactionDate] <= [LastYearPeriodEndDate] and [TransactionDate]  >= [LastYearPeriodBeginDate]

      then 1 else 0

      end

       

      LastYearPeriodEndDate and LastYearPeriodBeginDate are showing the correct results as expected but LastYearPeriodRange
      only results with 0’s.

      I expect to see records with LastYearPeriodRange =1 which will denote this transaction record falls between within the TransactionPeriod. 

       

      Does anyone have any thoughts?  Please let me know if I can clarify anything.

       

      Thanks!