Find missing customers between two dates in a snapshot facttable

[Anonymous]

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

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

[Anonymous]

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]

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