Customer Days Calculation Help

[#1]

Any ideas on how to approach calculation of customer days with in a period such as monthly, like below.  I need to calc customer days for cancel rate calculation and having trouble thinking through this one.  The concept is simple, but this one over my head at this point.

[#2]

Hi, you may want to restructure the data first and then create the straight table.

Raw:

LOAD * INLINE [

    Cust#, Contract Effective Date, Contract Expiration Date, Contract Cancel Date

    1, 1/1/2016, 31/12/2016,

    2, 15/2/2016, 14/2/2017, 6/5/2016

  3, 5/3/2016, 5/3/2017,

  4, 4/4/2016, 4/4/2017,

    5, 25/4/2016, 25/4/2017,

    6, 10/5/2016, 10/5/2017,

    7, 15/5/2016, 15/5/2017, 20/5/2016

];

Data:

load *,

if([Contract Cancel Date]='',[Contract Expiration Date],

  if([Contract Expiration Date]<[Contract Cancel Date],[Contract Expiration Date],[Contract Cancel Date])) as EndDate,

[Contract Effective Date] as StartDate

Resident Raw;

! ! !

FOR i=1 to NoOfRows('Data')

  LET startM=NUM(month(FieldValue('StartDate',$(i))));

  LET endM=num(month(FieldValue('EndDate',$(i))));

  LET startY=num(year(FieldValue('StartDate',$(i))));

  LET endY=num(year(FieldValue('EndDate',$(i))));

  LET startD=num(day(FieldValue('StartDate',$(i))));

  LET endD=num(day(FieldValue('EndDate',$(i))));

  LET MD = 12*($(endY)-$(startY))+($(endM)-$(startM))+1;

  LET CusValue = FieldValue('Cust#',$(i));

  for j = 1 to $(MD)

  ModifiedData:

  load

  $(CusValue) as Cust#_N,

  if($(j)=1,date(makedate($(startY),$(startM),$(startD))),date(monthstart(addmonths(makedate($(startY),$(startM),$(startD)),$(j)-1)))) AS StartDate_N,

  if($(j)=$(MD),date(makedate($(endY),$(endM),$(endD))),date(monthend(addmonths(makedate($(startY),$(startM),$(startD)),$(j)-1)))) as EndDate_N,

  num(

  if($(j)=$(MD),makedate($(endY),$(endM),$(endD)),monthend(addmonths(makedate($(startY),$(startM),$(startD)),$(j)-1))) -

  if($(j)=1,makedate($(startY),$(startM),$(startD)),monthstart(addmonths(makedate($(startY),$(startM),$(startD)),$(j)-1)))

  ,'#0') as #Days,

  monthname(if($(j)=1,makedate($(startY),$(startM),$(startD)),monthstart(addmonths(makedate($(startY),$(startM),$(startD)),$(j)-1)))) as month

  AutoGenerate 1;

  NEXT j;

NEXT i;

!

drop table Raw;

[#3]

Thank you. I will look at this ASAP—it’s a lot for me!

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post).

If not, please make clear what part of this topic you still need help with .

[#5]

The day calls are slightly off in some monthsI wonder if this is because we need to add 1 to certain calculations. I could live with minor variances like that for sake of simplicity, but this approach turns a 7 rows into over 480 rows and doubles the fieldsthis will make my data set too large to manage without my getting large hardware upgrade. Was hoping for something a little more dynamic and not tied to a month (i.e. week or day). I can appreciate how a dynamic function could affect app performance.

I am basically substituting cancel date for expiration date (if cancel date exists), checking if contract was effective in the period, and returning the days in period, unless it became effective within period or ended within period, then modifying calculation of days accordingly.

Is there a way to meet in middle so days calc can work for any time dimension and without creating an enormous table? Thx

[#6]

In the script given last time, I basically stacked the data, which make it easier to create the straight table. If you do not need to calculate for all months/weeks/days, instead, want to output the result based on single input (1 particular month for example), there is a easier way. May I know what's the end result in mind?

[#7]

I will be taking number of cancels over customer days for a cancel rate in a given day, week, month or other custom time dimensions.

[#8]

Hi Gary, in my opinion, if you want to create a table with time dimension, it is easier to have this dimension as a field in the data source. In current data table, it is easier to calculate customer day by customers, however, not by time dimensions, because time dimension is not part of the data...

[#9]

Thx. I don't mean time like minutes/seconds--my terminology could have been more clear. I mean time periods, like days, weeks, months, quarters, years, etc... I have more than 2 million customers. If I use your approach, my 2 million rows turns into 150 million or more, and that just allows for monthly calculation of customer days, when I need to be able to quantify customer days for any given day, week, month or combination of such.