Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Bobi
Partner - Contributor II
Partner - Contributor II

Subscription data (get order value per yearmonth from length of subscription)?

Hi. Is there any sustainable way to handle this subscription data-scenario. You have a fact-table with millions of rows. You have it per orderline-id and each orderline-id has a length of its subscription (like 6 months) and a created-date that is connected to a master calendar.

In the table/bar chart we want to see monthly value from all the subscriptions in every yearmonth. Meaning a Order created in jan 2022 with 100 k per month and 3 months subscription time will show 100 k in 2022-january, february and march. As of now it will only show 100 k in 2022-jan.

Is there any sustainable way to handle this? (The only I can think about will increase the fact-table times the avg amount of Binding time which will lead to many millions of rows and negative performance).

My assumption is that there is no better way to handle it but just wanted  to see if anyone might have any advice regarding this kind of subscription data-scenario?

Regards /

Labels (1)
3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Is the charge always for a full month or do you have partial months?

-Rob

Bobi
Partner - Contributor II
Partner - Contributor II
Author

Hi. It is allways for fullmonth. I have a created date on every orderline which is linked to a master calendar where I have Year, YearMonth and so on.

Orderline-id, created date, subscription number of months, monthly-income
123abc, 20222-01-01, 3,  5000

In this case it will only show 5000 on 2021-jan (YearMonth). But I want it to show 5000 in 2022 jan, 2022-feb and 2022-march in this case since the subscription period is 3 months here.

I think the only way is by a loop which will increase the fact table size from several millions to times avg(subscription number of months) and thereby significant loss in performance. 


Just wanted to make sure if I am correct here or if anyone has any smarter solution regarding this subscription-question?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

There may be several approaches, depending on how you want to roll things up and other requirements of the data model.  A very performant approach would be to create a link table based on Start Month / End Month like this:

2022-03-07_12-00-19.png

Then you could create charts by month and use "Sum of Rows" for totals.  I tested with 20M subscriptions and the chart was fast. 

2022-03-07_12-07-59.png

Here's a test script for 20M rows. You can change the number of rows on the "Autogenerate nnn" line. 

Subscriptions:
LOAD
  *,
  Date(MonthStart(StartDate), 'MMM YY') as StartMonth,
  Date(MonthStart(AddMonths(StartDate, NoOfMonths)), 'MMM YY') as EndMonth
;
LOAD
  RecNo() as SubscriptionID,
  Mod(RecNo(), 6) + 1 as NoOfMonths,
  Date(today(2) - Mod(RecNo(), 100)) as StartDate,
  ceil(rand() * 1000) as Rate
AutoGenerate 2e7;

Months:
LOAD Distinct
  Date(MonthStart(StartDate), 'MMM YY') as Month
Resident Subscriptions;

LinkTable:
  IntervalMatch (Month) LOAD DISTINCT StartMonth, EndMonth 
RESIDENT Subscriptions; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com