How to display add/remove and count of id?

[silambarasan]

Day-1:

ID,Date

A, 1-jan-2016

B,1-jan-2016

C,1-jan-2016

D,1-jan-2016

Day-2

ID,Date

A, 2-jan-2016

B,2-jan-2016

C,2-jan-2016

E,2-jan-2016

I want three tables

Table one display count of id on daily basis.

Table two display newly added id on daily basis

Table three display removed id.

table-1

MonthYear		1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
Jan-2016		4	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Feb-2016		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Mar-2016		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
Apr-2016		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
May-2016		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

if i click 2-jan in table-1 ,it will show list of new id added and list of existing id removed in two separate table

Eg:

Day-2

table-2

ID	newtype
E	new

table-3

ID	newtype
D	remove

johnw‌QlikView App Development‌hic‌gwassenaar‌swuehl‌

Message was edited by: Silambarasan M

[sunny_talwar]

Still trying to think what the best way would be to show the removed, but check out the first two tables in the attached application

[sujeetsingh]

Just do one thing is that join all the three tables in your model with date + ID as key.Now maintain three columns as ID from First table as ID_Pre, ID from second table as ID_Post.

Then you can derive a logic as IF ID_Pre<>ID_Post then 1 as ID Added some thing like that ...

[silambarasan]

Thanks for your help,same thing i did.I am also looking to find the removed id

[silambarasan]

could you please attach qvw file

[qliksus]

Are you looking for something like this

When you select 01/02/2016

 

When you select 01/03/2016

 

New Expression : (Count({1<Date={'$(=only(Date))'}>}  DISTINCT Date) and  count({1} DISTINCT Date)=1 )

Removed Exp : (Count({1<Date={'$(=only(Date))'}>}  DISTINCT Date)=0 and count({1<Date={"<$(=only(Date))"}>} DISTINCT Date)>=1 )

[silambarasan]

In your Removed Expression you shows all the removed ID till date but I want to know removed id from previous date not before than yesterday.

[qliksus]

Just change the Remove Expression as below

Removed Exp : (Count({1<Date={'$(=only(Date))'}>}  DISTINCT Date)=0 and count({1<Date={'$(=date(only(Date)-1))'}>} DISTINCT Date)>=1 )

[silambarasan]

qliksus please help..how to do this in back end scripting not in front end expression

[silambarasan]

How to find removed id?i am waiting for your response