0 Replies Latest reply on Nov 27, 2018 1:36 PM by malvika.tripathi.0

    Quarter to Date Calculation

    malvika.tripathi.0

      Hi,

       

      My quarter to date calculation is returning incorrect results. I want to show QTD records for all years. My calculation is returning all records for Q4 for previous years. I only want records from 10/1/17 to 10/31/17 (This will be QTD as of October), for example.

      I have a date calculation "Dateadd" that returns end of last month:  DATE(DATEADD('day',-1, DATETRUNC('month', TODAY())))-- This returns 10/31/2018

       

      My QTD calculation is :

       

      If

      DATETRUNC('quarter',[Acctg Dt] ) <= DATETRUNC('quarter',[Dateadd] )

      and

      DATETRUNC('quarter',[Acctg Dt] )  >=

      DATETRUNC('quarter', DATEADD('year', [Difference in Yr]*-1, [Dateadd]))

      THEN 1 ELSE 0 END

       

      Any help will be greatly appreciated.Happy to clarify some details. Attaching the workbook here. Thank you!!