Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Thanks in advance
David
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.
I would create separate Flag table with this:
LOAD
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.