2 Replies Latest reply: Jan 27, 2014 7:21 AM by Joonas Jäspi

# Help in script

Dear All, I'll appreciate your help in how to make a clculations within the sript.

I would like to calculate our customer-base with the following logic:

The data looks like this

 ID Connect Date Dis-Connect Date 1 01/01/2004 01/01/2004 2 02/01/2004 05/01/2004 3 02/01/2004 03/01/2004 4 02/01/2004 5 02/01/2004 04/01/2004 6 05/01/2004 7 05/01/2004 07/01/2004 8 05/01/2004 10/01/2004 9 05/01/2004 10 05/01/2004 11 09/01/2004 12/01/2004 12 09/01/2004

At each date I would like to have a number which is a calculation of the existing connected customers.

Means that, on the date of 2/1/2004 i will have 4 customers (5 connected till 2/1/04 and one dis-connected).

On the Feb/5 I will have 6 customers (10 connected and 4 dis-connected).

I'll appreciate your help on how to handle this in the script.

David

• ###### Re: Help in script

First step is to transform your data to this:

 ID Date Action Counter 1 1/1/2004 Connect 1 1 1/1/2004 Disconnect -1 2 2/1/2004 Connect 1 3 2/1/2004 Connect 1 4 2/1/2004 Connect 1 5 2/1/2004 Connect 1 3 3/1/2004 Disconnect -1 5 4/1/2004 Disconnect -1 6 5/1/2004 Connect 1 7 5/1/2004 Connect 1 8 5/1/2004 Connect 1 9 5/1/2004 Connect 1 10 5/1/2004 Connect 1 2 5/1/2004 Disconnect -1 7 7/1/2004 Disconnect -1 11 9/1/2004 Connect 1 12 9/1/2004 Connect 1 8 10/1/2004 Disconnect -1 11 12/1/2004 Disconnect -1

Then, it's a simply "Counter" sum operation.

• ###### Re: Help in script

I would create separate Flag table with this:

ID,

date([Connect Date]+Iterno()-1) as Date

if(iterno()=1, 1,0) as Flag_Start,

if[Connect Date]+iterno()-1= [Dis-Connect Date],0,1) as Flag_Connected

if[Connect Date]+iterno()-1= [Dis-Connect Date]1,0) as Flag_Disconnected

Resident Data

while [Connect Date]+iterno()-1) <= IF(isnull([Dis-Connect Date]),today(),[Dis-Connect Date]);

With this you can easily get number of connected ID with Sum(Distinct {<Flag_Connected={1}>} ID), but you can easily answer question like how many customers connected/disconnected this day/this month/this year by summing start and end flags.