Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Message was edited by: Silambarasan M
You might also want to look into something like described here:
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
Have a look at the sample, I've attached. You need to use CountDate as dimension in your chart table.
please attach that file
I already had in my second last answer, but reattaching to this one.
yes
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.
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;
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
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