How to count entered and removed id based on date?

[silambarasan]

How to count entered and removed id based on date?

Input table

Id.      Date

1        1-jan

2.       1-jan

1.       2-jan

2.       2-jan

3.       2-jan

1.       3-jan

3.       3-jan

3.       4-jan

5        4-jan

2        5-jan

Expected output

Using pivot table count number of new ID  enter everyday and number of existing ID remove everyday

Note:

1.   if same day one new ID enter and existing ID remove then display count value as 1-1.

2.  Eg.  Id-2 enter on 1st jan and removed on 4th jan. and again it(ID-2)  enter on 5th jan, so id-2 consider as new id

Month.     Day.   1.     2.      3.        4      5

Jan.                   2      1       1       1-1      1

hic‌swuehl‌johnw‌gwassenaar‌

Message was edited by: Silambarasan M

[swuehl]

You might also want to look into something like described here:

New/Lost/Returning/Loyal Customers

[silambarasan]

No i am getting wrong values.

1-jan-2016	2	0
2-jan-2016	1	1
3-jan-2016	0	1
4-jan-2016	1	2
5-jan-2016	1	0

Expected table:

Day    add     exit

1           2       0

2          1        0

3          0        1

4          1        1

5           1       2

Day 5 :  two ids removed and one id enter

[swuehl]

Have a look at the sample, I've attached. You need to use CountDate as dimension in your chart table.

[silambarasan]

please attach that file

[swuehl]

I already had in my second last answer, but reattaching to this one.

[silambarasan]

yes

[silambarasan]

Every thing works fine,but one modification.

If any ID removed on saturday and sunday,then we have to show that ids are removed on monday not saturday/sunday.

Instead of saturday and sunday, ids are removed on monday

please help this one.

[swuehl]

You can account for this using LastWorkDate( Date, 1):

INPUT: 
LOAD Id, SetDateYear(Date#(Date,'D-MMM'),2016) as Date INLINE [ 
Id, Date 
1, 1-jan 
2, 1-jan 
1, 2-jan 
2, 2-jan 
3, 2-jan 
1, 3-jan 
3, 3-jan 
3, 4-jan 
5, 4-jan 
2, 5-jan 
]; 


Let vMaxDate = Peek('Date',-1,'INPUT'); 

RESULT: 
LOAD Id, Date, LastWorkDate( Date(Date+1),1) as CountDate, Exit 
WHERE Date <> '$(vMaxDate)'; 
LOAD Id, Date, If(previous(Id)<>Id or previous(Date)-1<>Date,1) as Exit 
RESIDENT INPUT 
ORDER BY Id, Date Desc; 

CONCATENATE 
LOAD Id, Date, Date(Date) as CountDate, If(previous(Id)<>Id or previous(Date)+1<>Date,1) as New 
RESIDENT INPUT 
ORDER BY Id, Date asc; 

DROP TABLE INPUT; 

[silambarasan]

NO its not working.

Field values are not loaded.

Data set:

1, 4-jan-2016 (monday)

2, 4-jan-2016  

1, 5-jan-2016 (tuesday) 

2, 5-jan-2016

3, 5-jan-2016

1, 6-jan-2016 (wed)

3, 6-jan-2016

3, 7-jan-2016(thr)

5, 7-jan-2016

2, 8-jan-2016 (fri)

6, 11-jan-2016(mon)

Expected Output:

Date              New      Exit

4-jan-2016      2            0

5-jan-2016      1            0

6-jan-2016      0            1

7-jan-2016      1            1

8-jan-2016      1            2

11-jan-2016     1           1

Note:  9-jan and 10-jan no id's are enter.

         on 8-jan-2016  id 2 is enter and we have to show that id is removed on monday not saturday and sunday

please help with attachment

[swuehl]

This is what I get with my script (can't easily attach a QVW at the Moment):

CountDate	Sum(New)	Sum(Exit)
04.01.2016	2	0
05.01.2016	1	0
06.01.2016	0	1
07.01.2016	1	1
08.01.2016	1	2
11.01.2016	1	1

Can't really see a difference to your expected result