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

[Bobi]

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 /

[rwunderlich]

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

-Rob

[Bobi]

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]

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:

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

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