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