3 Replies Latest reply on Jun 13, 2018 7:40 AM by Mark Bradbourne

    Calculated Field that returns text if Date Dimension is between two dates

    Kim Domholt

      Hello.

       

      I can't get this calculated field to work. I want to return text if two dimensions meet given criteria, but i'm not sure how to format the dates in red. Any help would be appreciated.

       

      if [Status] = "Accepted" and [Amount Funded] <= 0.00 then "Accepted Opportunities" ELSEIF

      [Status] = "Accepted" and [Amount Funded] >= 1.00 then "Funded Opportunities" ELSEIF

      [Status] = "Pipeline" and [CnPrProp 1 Date Expected] <= DATE("2015-12-31") then "Pipeline - Past" ELSEIF

       

       

      [Status] = "Pipeline" and [CnPrProp 1 Date Expected] DATE("2016-01-01") AND DATE ("2016-12-31")then "Pipeline 2016" ELSEIF

      [Status] = "Pipeline" and [CnPrProp 1 Date Expected] DATE("2017-01-01") AND  DATE ("2017-12-31")then "Pipeline 2017" ELSEIF

      [Status] = "Pipeline" and [CnPrProp 1 Date Expected] DATE ("2018-01-01") AND  Date ("2018-12-31")then "Pipeline 2018" ELSEIF

      [Status] = "Pipeline" and [CnPrProp 1 Date Expected] DATE ("2019-01-01") AND  DATE ("2019-12-31")then "Pipeline 2019" ELSEIF

      [Status] = "Pipeline" and [CnPrProp 1 Date Expected] DATE ("2020-01-01") AND  DATE ("2020-12-31")then "Pipeline 2020" ELSEIF

      [Status] = "Pipeline" and [CnPrProp 1 Date Expected] DATE ("2021-01-01") AND  DATE ("2021-12-31")then "Pipeline 2021" ELSEIF

      [Status] = "Pipeline" and [CnPrProp 1 Date Expected] Date ("2022-01-01") AND  DATE ("2022-12-31")then "Pipeline 2022" ELSEIF

       

       

      [Status] = "Proposal" then "Opportunities Presented" ELSEIF

      [Status] = "Declined" then "Declined" else [Status]

      END

       

      Thanks.

       

      Kim