Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

MVP
MVP

Re: How to count entered and removed id based on date?

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

New/Lost/Returning/Loyal Customers

silambarasan
Contributor

Re: How to count entered and removed id based on date?

No i am getting wrong values.

1-jan-201620
2-jan-201611
3-jan-201601
4-jan-201612
5-jan-201610

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

MVP
MVP

Re: How to count entered and removed id based on date?

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

silambarasan
Contributor

Re: How to count entered and removed id based on date?

please attach that file

MVP
MVP

Re: How to count entered and removed id based on date?

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

silambarasan
Contributor

Re: How to count entered and removed id based on date?

yes

silambarasan
Contributor

Re: How to count entered and removed id based on date?

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.

Highlighted
MVP
MVP

Re: How to count entered and removed id based on date?

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
Contributor

Re: How to count entered and removed id based on date?

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

MVP
MVP

Re: How to count entered and removed id based on date?

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

Community Browser