Monthly data comparison and finding newly added items

[#1]

Hello!

I am currently working on one problem which seems to be complicated to me. I have monthly data which I need to compare. I always compare two neighbour month(9&10, 10&11,etc). Every month new items are added to the list of existing ones and I new to find and count them. My current table structure looks like this:

Table1. Total = 3 items

Item	Field1	Field2	MonthNo
A	1	1	10
B	2	1	10
C	2	3	10

Table2. Total = 6 items

Item	Field1	Field2	MonthNo
A	1	1	11
B	2	1	11
C	2	3	11
F	1	2	11
D	2	1	11
G	2	2	11

So the task is to find new items added in each month (always comparing latest and previous months) which meet the criteria: Field1=2 and count them. Answer in this case(months 11 and 10): Items count=2 (Items D and G meet the criteria).

I can get the monthly total number of distinct items and also number of items where Field1=2 but I need to compare the actual items codes in each month, find new ones(that didn’t exist in previous month) and then count them. Is there any solution for this problem?

[Gysbert_Wassenaar]

Try searching for new+lost: http://community.qlik.com/search.jspa?q=new+lost

You should encounter for example these discussions:

http://community.qlik.com/message/678525#678525

http://community.qlik.com/message/262719#262719

That said you can easily identify new items in the load script if your source data is already ordered by month:

LOAD

     Item,

     Field1,

     Field2,

     MonthNo,

     if(not exists Item, 1, 0) as IsNew,

     if(Field1 = Field2) as IsEqualField1Field2

FROM ...source.

You can count the new items with count({<IsNew={1},IsEqualField1Field2={1}>}distinct Item)