Link date range to monthly period

[martyn_birzys]

I have transactional subscription data, which may cover one month period or period of one year.   

CustID	SubscriptionValue	SubsStartDate	SubsEndDate
345	1980	01/06/2015	31/05/2016
123	145	19/05/2016	18/06/2016
123	145	19/04/2016	18/05/2016
123	145	19/03/2016	18/04/2016
123	199	19/02/2016	18/03/2016

My required output is Revenue each month. So for example March calculation looks like this

CustID	SubscriptionValue	SubsStartDate	SubsEndDate	SubsDays	DailySubsRate	Mar16	SubsDaysInMarch	SubsChargeMarch
345	1980	01/06/2015	31/05/2016	366	5.409836066	31	31	167.7
123	145	19/05/2016	18/06/2016	31	4.677419355	31	0	0
123	145	19/04/2016	18/05/2016	30	4.833333333	31	0	0
123	145	19/03/2016	18/04/2016	31	4.677419355	31	12	56.13
123	199	19/02/2016	18/03/2016	29	6.419354839	31	19	121.97
123	199	19/01/2016	18/02/2016	31	6.419354839	31	0	0

So my output should be something like this for each month up to 12 month ahead.  

CustID	Month	Revenue
345	Jan-16	167.7
345	Feb-16	167.7
345	Mar-16	167.7
345	Apr-16	167.7
345	May-16	167.7
123	Feb-16	199
123	Mar-16	178.1
123	Apr-16	145
123	May-16	145
123	Jun-16	84.19

I was reading Henric Cronström's Blog and looking at IntervalMatch examples, and this would probably work

IntervalMatch(_Month_)

LOAD SubsStartDate, SubsEndDate

however, _Month_ is not a fixed date, it is a whole month period. I need to link range to a range, technically speaking.

All suggestions and solutions will be greatly appreciated, thank you for looking into this.

[sunny_talwar]

May be this:

Table:

LOAD *,

  SubsEndDate - SubsStartDate + 1 as Days,

  SubscriptionValue / (SubsEndDate - SubsStartDate + 1) as SubscriptionValuePerDay,

  MonthName(SubsEndDate) as EndMonthYear,

  MonthName(SubsStartDate) as StartMonthYear;

LOAD * INLINE [

    CustID, SubscriptionValue, SubsStartDate, SubsEndDate

    345, 1980, 01/06/2015, 31/05/2016

    123, 145, 19/05/2016, 18/06/2016

    123, 145, 19/04/2016, 18/05/2016

    123, 145, 19/03/2016, 18/04/2016

    123, 199, 19/02/2016, 18/03/2016

];

FinalTable:

LOAD CustID,

  MonthName(AddMonths(StartMonthYear, IterNo() - 1)) as MonthYear,

  SubscriptionValuePerDay * (Floor(MonthEnd(AddMonths(StartMonthYear, IterNo() - 1))) - Floor(MonthStart(AddMonths(StartMonthYear, IterNo() - 1))) + 1) as Revenue

Resident Table

While AddMonths(StartMonthYear, IterNo() - 1) <= EndMonthYear;

DROP Table Table;

[santiago_respane]

Hi Martyn,

below you will find a way to resolve it.

Table:

LOAD CustID,

  SubscriptionValue,

  DATE#(SubsStartDate,'DD/MM/YYYY') AS SubsStartDate,

  DATE#(SubsEndDate,'DD/MM/YYYY') AS SubsEndDate

  ;

LOAD * Inline [

CustID,SubscriptionValue,SubsStartDate,SubsEndDate

345,1980,01/06/2015,31/05/2016

123,145,19/05/2016,18/06/2016

123,145,19/04/2016,18/05/2016

123,145,19/03/2016,18/04/2016

123,199,19/02/2016,18/03/2016

];

FinalTable:

LOAD CustID,

  DATE(AddMonths(SubsStartDate,IterNo() -1),'MMM-YY') AS Month,

  SubscriptionValue

RESIDENT Table

WHILE AddMonths(SubsStartDate,IterNo() -1) <= SubsEndDate;

Please copy this into an empty qv app and tell me if this fits your needs so we can continue with the rest of your requirements (revenue split, etc.).

Kind regards,

[martyn_birzys · Accepted solution]

Thanks for putting me on the right track, with a bit of tinkering I've got it working, see below. FinalTable:

LOAD CustID,
  MonthName(AddMonths(StartMonthYear, IterNo() - 1)) as MonthYear,
  StartMonthYear as thevariable,
  if (date(floor(date(SubsStartDate))) <= date(floor(AddMonths(MonthStart(SubsStartDate), IterNo() - 1))),
     date(floor(AddMonths(MonthStart(SubsStartDate), IterNo() - 1))),
          date(SubsStartDate)) as PeriodStartDate,
  if (date(floor(date(SubsEndDate))) >= date(MonthEnd(AddMonths(SubsStartDate, IterNo() - 1))),
     date(MonthEnd(AddMonths(SubsStartDate, IterNo() - 1))),
          date(SubsEndDate)) as PeriodEndDate,
  SubscriptionValuePerDay,
  SubsStartDate,
  SubsEndDate
Resident Table
While AddMonths(StartMonthYear, IterNo() - 1) <= EndMonthYear;

DROP Table Table;

FinalTable2:
LOAD *,
  PeriodEndDate-PeriodStartDate as PeriodServiceDays,
  (PeriodEndDate-PeriodStartDate) * SubscriptionValuePerDay as PeriodValue
Resident FinalTable;

DROP Table FinalTable;