Aggregating subscribers over time if still active

[YReisner]

Hi everyone, I have some financial receipt data of customers, and I would like to show how many subscribers exist over time. 

The data consists of a subscriber id, start_date of the subscription, and end_date, depending on the type of subscription (yearly, monthly etc). I also have the amount_paid.

I would like to show a graph with monthName dimension, showing the amount of subscribers of every month. However, if I use monthname(start_date) as the dimension, I lose all the subscribers that have a different start_date, but are still active.

For example, a yearly subscriber that start on January 2024 is still active on February, March etc all the way until January 2025, but his start_date would not reflect that.

With SQL I would just join this table on a list of dates with some rules and aggregate, but I'm wondering what's the best way to create this chart using Qlik analytics.

Thank you very much for your help!

[Sayed_Mannan · Accepted solution]

In Qlik Sense, you can create a master calendar and link it to your data. This will allow you to count the number of active subscribers for each month. Here's how you can do it:

First, create a master calendar in your load script:

Temp:
Load
min(start_date) as minDate,
max(end_date) as maxDate
Resident YourTable;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

Drop Table Temp;

MasterCalendar:
Load
Date($(varMinDate) + IterNo() - 1) as Date
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Then, link your data to the master calendar:

Left Join (MasterCalendar)
Load
subscriber_id,
start_date,
end_date
Resident YourTable;

Now, you can create a chart with `monthName(Date)` as the dimension and use the following expression to count the number of active subscribers for each month:

Count({<Date = {">=$(start_date)<=$(end_date)"}>} DISTINCT subscriber_id)

This expression will count the number of unique `subscriber_id` where the `Date` is between `start_date` and `end_date`.

This will give you the number of active subscribers for each month. Remember to replace `YourTable` with the name of your table.