topic Re: Find missing customers between two dates in a snapshot facttable in QlikView

Find missing customers between two dates in a snapshot facttable

Anonymous — Wed, 29 Jul 2015 23:48:12 GMT

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 No	Date	Is Subscriber
1	2.7.2015	1
2	2.7.2015	1
3	2.7.2015	1
4	2.7.2015	1
5	3.7.2015	1
4	3.7.2015	1
3	3.7.2015	1
1	3.7.2015	1
2	3.7.2015	1

How can this be solved with Sense?

Regards

Birkir

Re: Find missing customers between two dates in a snapshot facttable

Gysbert_Wassenaar — Thu, 30 Jul 2015 05:21:14 GMT

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...

Re: Find missing customers between two dates in a snapshot facttable

Anonymous — Thu, 30 Jul 2015 11:05:46 GMT

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

Re: Find missing customers between two dates in a snapshot facttable

marcus_malinow — Thu, 30 Jul 2015 11:14:25 GMT

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

Re: Find missing customers between two dates in a snapshot facttable

Gysbert_Wassenaar — Thu, 30 Jul 2015 11:20:11 GMT

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])