Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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)
try,
sum({$<[Payment Date]={">=$(=date(max([Invoice Date])+30))"}>}Amount)
There is no aggregation calculation in your sub set analysis which is Max, Min, Sum, Count. can you explain the requirement?
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
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)
the first option is not working and probably I have to go to change the datamart to flag it. thanks Anil
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)
Thanks Anil, it is working