Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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
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
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])