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: 
Anonymous
Not applicable

Find missing customers between two dates in a snapshot facttable

Hi All,

I have a snapshot fact table holding subscriber data. I want to display a KPI thats shows a number of new subscribers joined yesterday.

Here is a sample data or similar

   

Customer NoDateIs Subscriber
12.7.20151
22.7.20151
32.7.20151
42.7.20151
53.7.20151
43.7.20151
33.7.20151
13.7.20151
23.7.20151

How can this be solved with Sense?

Regards

Birkir

4 Replies
Gysbert_Wassenaar

If your source data is in chronological order (i.e. sorted by date) then you can use the Exists function to check if a customer number already exists in the records loaded:

LOAD

     [Customer No],

     Date,

     [Is Subscriber],

     If(Exists([Customer No]),'No','Yes') as IsNew

FROM ...source_table...


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

Hi Gysbert and all,

Thanks for the input I could sort the table and load it like this, but I have a small twist. What if I would like to count all new customers joined after 01.07.2015 until today for example 30.07.2015. I have done this with SQL by distinct count customerId on the 01.07.2015 and then count the ones that are subscribers today and dont exist in the first set.

Could I do this via Set Analysis?

Regards

Birkir

marcus_malinow
Partner - Specialist III
Partner - Specialist III

Yes -

Using Gysbert's flag

Sum({$<Date={">01.07.2015"}>} IsNew)

You may need to do some manipulation of the date value so that it matches that in your data

Gysbert_Wassenaar

Or something a lot more complicated. If you select a date then you can try this:

count({<[Customer No] *= e({<Date={"<=$(=only(Date))"}>}[Customer No])>}[Customer No])


talk is cheap, supply exceeds demand