Forum Discussion

wierdtonic's avatar
wierdtonic
Data Storage
04-24-2022

What is the best way to query number of customers subscribed during a particular time period

Our dashboard users have a specific request; they want to see data within a particular time period
Below is how my dataset looks like (dates are mm/dd/yy).

User request - To see how many customers were subscribed within a time period regardless of their current status. i.e. even if their current status is cancelled as long as they were active during the user-provided time period they should be counted

Example - User selects time range to be 01/01/2020 - 03/31/2020. Running a query on below data set should return count of 3. [CUST1 as they cancelled after 03/31/2020, CUST3 as they signed up before 01/01/2020 but are still active, CUST5 as they were active for some point during that period]

Problem - While I can write a SQL query with abundant where clauses to achieve this, we want a self-service automated way i.e. we want users to just provide us the time range and get the number. How do we achieve this in Sisense? I am also open to other tools, changing the data model design and other options. 
 
Customer ID
Subscription Start Date
Subscription End Date
Subscription Status
CUST1
10/11/2019
4/12/2020
Cancelled
CUST2
1/12/2020
 
Active
CUST3
5/1/2019
 
Active
CUST4
6/7/2012
7/8/2012
Cancelled
CUST5
1/12/2020
3/14/2020
Cancelled
CUST6
4/12/2020
 
Active
 
Data Warehouses

1 Reply

Replies have been turned off for this discussion
  • HamzaJ's avatar
    HamzaJ
    Data Integration
    04-24-2022

    Hey wierdtonic 

    I think I understand your issue. Due to the fact that these are start and enddates selecting a period works a bit differently for you (/your users) to get the needed result.

    For example, if you want this period 01/01/2020 - 03/31/2020

    You should set Start Date to <= 03/31/2020 and end_date to > 01/01/2020

    If you convert your dates to int (03/31/2020 becomes 20200331) this will be a bit easier to do. 

    Another solution would be to join your table with a date-table. This is how we did it. Instructions found here

    By joining this date-table with your table you will create a date for each day between start and end (if end is empty then now). This way your users can filter on this Day-field you created. This will select the right records from the database as long as at least one day is in that filtered period. 

    Hamza