Date calculation with both fixed and unfixed end date

[#1]

Hi all,

I have a customer file with a field "date of subscription" and a field "date of unsubscription". Both customers (still active and unsubcripted) are included in the file. For 'still active' customers, there is just "-" as data in "date of unsubscription".

I'd like to count the number of days customers get a subscription. I have thus two problems :

• write the good formula to calculate between 2 dates
• evaluate each day who are the still active customers and recalculate the datas for them.

I'd be glad if someone could help me. This is not very easy for a beginner coming from Excel,

Thanks

Julien

[#2]

We often use the function Today() for an open end date. That gives you a way of calculating the number current subscription days (active subscription has date today).

To calculate between two days, you only have to subtract them from each other to get the number of days.

[#3]

Hi Julien, I'd suggest the same as Mark - use a variable field for current date to give you a value for calculating the number of subscription days for active customers.

I'd also suggest creating a flag (this can be done within your load script) to idently active customers, e.g. if "date of unsubscription" is null then 1 else 0 (the 1 denoting an active customer, the 0 denoting an inactive customer). This will give you a way to compare subscriptions between the two customer groups.

[#4]

Thank you Mark.

I thought I needed to use Today () but I was not sure exactly how and where to do it. The tips of Nicky emphasizes your advice.

Julien

[#5]

Thank you Nicky,

I gonna try with your solution as well