topic Re: Customer Days Calculation Help in QlikView

Customer Days Calculation Help

Wed, 24 Aug 2016 05:53:25 GMT

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.

Re: Customer Days Calculation Help

Thu, 25 Aug 2016 02:50:52 GMT

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;

Re: Customer Days Calculation Help

Thu, 25 Aug 2016 16:05:06 GMT

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

Re: Customer Days Calculation Help

oknotsen — Thu, 25 Aug 2016 16:10:22 GMT

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 .

Re: Customer Days Calculation Help

Fri, 26 Aug 2016 17:11:10 GMT

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

Re: Customer Days Calculation Help

Mon, 29 Aug 2016 01:27:04 GMT

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?

Re: Customer Days Calculation Help

Mon, 29 Aug 2016 14:55:05 GMT

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.

Re: Customer Days Calculation Help

Tue, 30 Aug 2016 02:03:27 GMT

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...

Re: Customer Days Calculation Help

Tue, 30 Aug 2016 07:59:19 GMT

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.