4 Replies Latest reply: Jul 10, 2017 9:52 AM by Joao Carapinha RSS

    Sum values if date is less than minimum date

    Joao Carapinha

      Hi All,

       

      I'm working on the below expression and wondering how I should change it to fit my study objective.

      The expression "Sum First Year" sums the measure "Paid_Amount' within unique groups "Benkey" if the date dimension "Claim_Service_Date_Formatted" is less than or equal to 20160101.

      sum(aggr(sum( {$<  "Claim_Service_Date_Formatted" = {"<=20160101"}>} Paid_Amount),Benkey))

      The above expression works as expected.

       

      However, my study objective is to change this part of the expression {"<=20160101"} to sum when the date dimension "Claim_Service_Date_Formatted" is less than or equal to the mininum date of a second date dimension "First_Year".

       

      This is what my data looks like:

       

       

      I thought of changing it to {“<=(=Min(First_Year))”}  but when I apply it, I get a bunch of zeros for all rows.

       

      The full expression would look like this:

      sum(aggr(sum( {$<  "Claim_Service_Date_Formatted" = {“<=(=Min(First_Year))”}>} Paid_Amount),Benkey))

       

      Thanks so much for any help you can provide!