Sum only if the difference between two fields are greater than a certain amount (30 days).

[ronaldwang]

how to use expression to Sum only if the difference between two fields are greater than a certain amount (30 days). Say if the two fields are paymentdate and invoice date

I have tried

sum({$<[Payment Date]={">=[Invoice Date]+30"}>}Amount)

[shraddha_g]

try,

sum({$<[Payment Date]={">=$(=date(max([Invoice Date])+30))"}>}Amount)

[Anil_Babu_Samineni]

There is no aggregation calculation in your sub set analysis which is Max, Min, Sum, Count. can you explain the requirement?

[ronaldwang]

The requirement is that for a number of transactions (where  [invoice date] and [Payment date] is 2 of the fields. And I want to add up the amount where the difference between the [invoice date] and [Payment date] is less than 30 days. (based on the assumption that I am not to load the [Payment date]-[invoice date] into my data model. thanks

[Anil_Babu_Samineni]

May be this?

Sum({<[invoice date] = {"=([invoice date] - [Payment date]) < 30"}>} Amount)

OR

In script, create one flag

If([invoice date] - [Payment date] < 30, 1, 0) as Flag

OR

If(NetWorkDays([invoice date] - [Payment date]) < 30, 1, 0) as Flag

And set analysis should be

Sum({<Flag = {1}>} Amount)

[ronaldwang]

the first option is not working and probably I have to go to change the datamart to flag it. thanks Anil

[Anil_Babu_Samineni · Accepted solution]

I assume, First one also should work. Might this problem with Date.

Again,

If(Interval(Date(Date#([invoice date],'Having Format'),'Required Format') - Date(Date#([Payment date],'Having Format'),'Required Format'), 'DD') < 30, 1, 0)

[ronaldwang]

Thanks Anil, it is working