topic Date calculation with both fixed and unfixed end date in QlikView

Date calculation with both fixed and unfixed end date

Wed, 20 Jan 2010 19:06:21 GMT

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

Date calculation with both fixed and unfixed end date

Wed, 20 Jan 2010 19:27:40 GMT

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.

Date calculation with both fixed and unfixed end date

Wed, 20 Jan 2010 21:49:43 GMT

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.

Date calculation with both fixed and unfixed end date

Thu, 21 Jan 2010 10:37:42 GMT

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

Date calculation with both fixed and unfixed end date

Thu, 21 Jan 2010 10:56:58 GMT

Thank you Nicky,

I gonna try with your solution as well