6 Replies Latest reply on Jun 16, 2018 5:16 PM by Jalal Hassan

    DATERUNC Calculations & Aggregations

    Jalal Hassan

      Hi,

       

      So I'm attempting to do a DATERUNC calculation, and for the life of me I cannot get it to pull the correct data with aggregations.

       

      (I have a secondary data source that I need location data from to mix with my financial data)

       

      The Two Calculations are below,

       

      Prior Period

      --------

      IF
      DATETRUNC([Period Length],([Book Date]))=
      DATETRUNC([Period Length],DATEADD([Period Length],-
      IIF([Period Comparison]=1,1,
      IIF([Period Comparison]=2 AND [Period Length]="day",365,
      IIF([Period Comparison]=2 AND [Period Length]="week",52,
      IIF([Period Comparison]=2 AND [Period Length]="month",12,
      IIF([Period Comparison]=2 AND [Period Length]="quarter",4,
      IIF([Period Comparison]=2 AND [Period Length]="year",1,0)))))),[Period End Day]))
      AND
      DATETRUNC('day',([Book Date]))<=
      DATETRUNC('day',DATEADD([Period Length],-
      IIF([Period Comparison]=1,1,
      IIF([Period Comparison]=2 AND [Period Length]="day",365,
      IIF([Period Comparison]=2 AND [Period Length]="week",52,
      IIF([Period Comparison]=2 AND [Period Length]="month",12,
      IIF([Period Comparison]=2 AND [Period Length]="quarter",4,
      IIF([Period Comparison]=2 AND [Period Length]="year",1,0)))))),[Period End Day]))
      
      
      THEN
      
      
      SUM([Backoffice Properties].[Properties ])
      
      
      
      
      
      
      END
      

       

       

       

      Current Period

      --------

      IF
      (DATETRUNC([Period Length], ([Book Date])) =
      DATETRUNC([Period Length], [Period End Day])
      AND
      DATETRUNC('day', ([Book Date])) <=
      DATETRUNC('day', [Period End Day]))
      
      
      THEN
      
      
      SUM([Backoffice Properties].[Properties ])
      
      
      END
      

       

       

      I'm not sure how to keep the purpose of the calculation and also get it to return the correct values.

       

      Current I have been trying to attempt a MIN/MAX of the (Book Date), but it turns into just returning the same values for both periods.

       

      Any help would be appreciated.

       

      Regards,

       

      Jalal Hassan