New calculated time field

[gerhardl]

Hi,

I am loading a table with the following two fields:

1. Date of last payment
2. Date of last purchase

I would like to create an additional field called "Days since last movement".

This must look at whatever is the latest date of the two above-mentioned fields, and then subtract that from TODAY's date.

Example:

• Date of last payment - 2011/04/10
• Date of last purchase - 2011/04/01
• Days since last movement = 4

Possible?

Gerhard

[IAMDV]

Create two variables...

vMaxLastPayment = Max({1} [Date of last payment])

vMaxLastPurchase = Max({1} [Date of last purchase])

Then use Max fucntion with the variables like this...

=Today() - Max($(vMaxLastPayment) , $(vMaxLastPurchase))

[#3]

try this

load date2, date3, if(date2>date3, interval(date(today())- date(date2),'DD'), interval(date(today())- date(date3),'DD')) as movement

[erichshiino]

Try something like this:

Load *,

today()-if(LastPayment>LastPurchase,LastPayment,LastPurchase) as DaysLastMovement

resident table;

Regards,

Erich

[rwunderlich · Accepted solution]

today(1)-RangeMax(LastPurchase,LastPayment) as DaysLastMovement