Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
martyn_birzys
Creator
Creator

Link date range to monthly period

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

CustIDSubscriptionValueSubsStartDateSubsEndDate
345198001/06/201531/05/2016
12314519/05/201618/06/2016
12314519/04/201618/05/2016
12314519/03/201618/04/2016
12319919/02/201618/03/2016

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

CustIDSubscriptionValueSubsStartDateSubsEndDateSubsDaysDailySubsRateMar16SubsDaysInMarchSubsChargeMarch
345198001/06/201531/05/20163665.4098360663131167.7
12314519/05/201618/06/2016314.6774193553100
12314519/04/201618/05/2016304.8333333333100
12314519/03/201618/04/2016314.6774193553112                    56.13
12319919/02/201618/03/2016296.4193548393119                  121.97
12319919/01/201618/02/2016316.4193548393100

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

CustIDMonthRevenue
345Jan-16167.7
345Feb-16167.7
345Mar-16167.7
345Apr-16167.7
345May-16167.7
123Feb-16199
123Mar-16178.1
123Apr-16145
123May-16145
123Jun-1684.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.

1 Solution

Accepted Solutions
martyn_birzys
Creator
Creator
Author

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;

View solution in original post

3 Replies
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;

Capture.PNG

santiago_respane
Specialist
Specialist

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
Creator
Creator
Author

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;