Sum values if date is less than minimum date

[#1]

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!

[sunny_talwar]

Can you elaborate a little on how the expected output needs to look like? (in numerical terms)

[#3]

Sure, thanks for your swift reply.

There are groups with a unique identier - Benkey

Presented below are only two, one above and another below the red line.

The amounts in the yellow box will be summed if any of the dates in 2nd column are less than the lowest (earliest) date in the 3rd column.

In the top group, because all dates are less than 20170121, the amounts in the yellow box will be summed and place in the top right line.

In the bottom group, only 5 dates in the green box are less than the lowest/earliest date in the 3rd column (20140515), so only the amounts in the yellow box will be summed and placed in the top right line.

Really appreciate your help with this.

[martinpohl · Accepted solution]

It isn't easy as it should.

Use

sum( if( aggr(nodistinct min(First_Year), Benkey) >= Claim_Service_Date_Formatted, Paid_Amount, 0))

Regards

[#5]

Absolutely awesome, thanks so much!