Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Edvin
Creator
Creator

How to eliminate all the ID's in count() that exists in previous weeks

Hey all,

So yeah, as the title mentions, I am calculating how much there are records based on some criteria, and report it weekly.

The main issue is that, some ID's can be seen in one week and in another (in table chart).

I need to count how many ID's in every week, but eliminate if that ID exists in earlier weeks.

For example:

If an ID is calculated as occurred in week 37, week 38 and week 40. I need only to calculate that ID in week 37 and not take into count() for week 38 and week 40.

Sorry for my English, not so good at explaining things.

I attach photo from my Qlik app, which is table chart, how do I report it, and a small excel sample from my data.

example table chart.JPG

In the photo of table chart, this is how my measures looks like:

count(distinct if(RECORD_USERID='SLFSRV',[ID]));

Another one:

count(distinct if(ITEM_CODE='SOMETHING',[ID]))

Pretty simple measures.

As for the .XLSX file, the last ID is what exactly I was talking about.

Edvin_0-1607083026211.png

I got ID occurred in several weeks, which is week 37, week 40 and week 44. As I calculate how much ID's there were in my table chart, this ID will be counted on week 37, week 40, week 44. Eliminate all the ID's if the ID's exists in previous weeks.

I really hope that you all understood, what I was trying to say 🙂

 

Oh, and my script is really simple and looks like this:

LOAD CASE&'/'&SUBCASE as "ID",

ITEM CODE,

year(date(floor(RECORD_TIMESTAMP)))&'-'&week(date(floor(RECORD_TIMESTAMP))) as "Year-Week",

date(floor(RECORD_TIMESTAMP)) as "Data",

RECORD_USERID;

SELECT.....

FROM ....;

5 Replies
GaryGiles
Specialist
Specialist

This should count an ID only in the first week that it occurs:

Count(Distinct if(Date=aggr(Min(Date),ID),ID))

Edvin
Creator
Creator
Author

@GaryGiles  something's not right.

I got data from week 36 to week 49 (previous week), got about 8000~ unique ID records, but when I do it your way I'm left with 4000~, which is why it is wrong.

GaryGiles
Specialist
Specialist

Can you post a bigger sample set?

Edvin
Creator
Creator
Author

Sure, no problem.

Got like 126k rows in all data set, each minute increasing, and about 8000 unique ID's.

I somehow think I need to do it somehow in script with peek(), but have no clue...

Edvin
Creator
Creator
Author

Guys, anyone @Kushal_Chawda , @sunny_talwar ?