topic Re: Sum only if the difference between two fields are greater than a certain amount (30 days). in QlikView

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

ronaldwang — Wed, 27 Sep 2017 03:38:43 GMT

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)

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

shraddha_g — Wed, 27 Sep 2017 03:41:11 GMT

try,

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

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

Anil_Babu_Samineni — Wed, 27 Sep 2017 05:54:17 GMT

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

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

ronaldwang — Wed, 27 Sep 2017 06:01:45 GMT

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

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

Anil_Babu_Samineni — Wed, 27 Sep 2017 06:27:21 GMT

May be this?

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

In script, create one flag

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

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

And set analysis should be

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

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

ronaldwang — Wed, 27 Sep 2017 06:45:16 GMT

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

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

Anil_Babu_Samineni — Wed, 27 Sep 2017 06:49:08 GMT

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)

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

ronaldwang — Wed, 27 Sep 2017 06:58:17 GMT

Thanks Anil, it is working