For the new ID's the amount sold is 0? If so you can create a table that shows only the ID's where sold = 0. Then if a user selects a date in the listbox then you see only the new ID's for that date. Is that what you need?
Use a calculated dimension for the ID's: if(Sold = 0, ID) and a listbox for selection the date.
Sorry, maybe I can't explain what I want to get correctly, my English is not perfect. I thought, that my example will clarify what I want to get.
Let me try again.
I've changed the example (removed 0 from sold):
ID Date Sold Price 4448 04.2012 10,00 990,00 4448 05.2012 20,00 990,00 4499 04.2012 1,00 1 890,00 4544 04.2012 2,00 2 090,00 4595 04.2012 3,00 2 590,00 4624 05.2012 56,00 3 190,00 5096 05.2012 5,00 2 390,00
This data is the small part of real data (for 5 years). Imagine, that this is a full set of data. For this table I want to get the new IDs in 05.2012, comparing to the previous month 04.2012.
There is no special attribute in data for new IDs, only ID and date, so I need to compare all IDs in 04.12 with all IDs in 05.12 to find the difference. As you see its 4624 and
5096, this IDs appeared only in 05.2012, but I need an expression, which will work on any amount of data.
In report it will work in next way:
A user will choose any (but only one) month and will see the small table or list with new ID in this month comparing to previous month.
May be there is a way to show this new IDs only knowing IDs and dates?
I think I can get the result you want. I added a flag that signals when an ID first appears in the record set. For this to work you do need to make sure the data is ordered by date If necessary you can add that clause to the load statement.
Can you check if the attached qvw does what you need?
qcomNewIDs.qvw 140.5 K
Thank you, Gysbert.
It works perfect on example, but when I add this flag to my existing script - no luck.
First of all, I load data to Qlik from SQL DB using OLEDB and SQL Select script. As I understood, I can't use EXISTS function in SQL Select, only in LOAD statement, am I correct?
So, I've tried to make a LOAD RESIDENT script after SQL select to add a flag with EXISTS function, but it doesen't work, it shows only zeroes.
I found, that EXISTS function stop working, when you use it for LOAD RESIDENT script. You can try it, first to load data and then to add a NewFlag to a table:
if(Exists(ID),0,1) as NewFlag
DROP Table Items;
Maybe I'm doing something wrong?
I'm not sure what you mean by "comparing to previous month", but the attached example shows one possible interpretation and solution - an "AsOf" table. It uses years instead of months, but is otherwise very similar to your example.
Edit: Hmmm, no, I'm pretty sure I misinterpreted what you were after. Sorry.
Edit 2: gwassenar's solution of setting a flag during the load seems good.
testPreviousYear.qvw 154.2 K