Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

darrin_pilkingt
Contributor II

End of month totals based on dates around each month end

Hello everyone. 

I am trying to calculate the total number of active customers at the end of each month when selecting multiple months.  I am doing this by using the StartDt and DisconnectDt of their service.

Example:

So if a customer has a StartDt of 7/1/2016 and a DisconnectDt of 8/5/2016 they would show as active for Jul-2016 but not for Aug-2016. Even though they were active in August they disconnected before the last day of August.

Currently I am able to do this for the last date in my selection below.  DateKey is the dates in my calendar used in month selections.

sum( {$ <ActivationDt= {"<=$(=date(max(DateKey)))"},[DisconnectDt Closed]= {">$(=date(max(DateKey)))"}>} CustCnt)


AccountIDStartDtDisconnectDtDistconnectDt ClosedCustCnt
001

6/2/2015

12/5/201612/5/20161
0022/22/2016null1/1/20201
0034/12/20161/18/20171/18/2017

1

0041/10/20171/21/20171/21/20171
0051/20/2017Null1/1/2020

1

0067/14/20162/2/20172/2/20171


I created a future date column "DisconnectDt Closed" that has a future disconnect date, "1/1/2020", to deal with the Null value when a customer has not disconnected.  Otherwise the actual disconnect date is populated.

Remember the goal is to display the total number of customers active at the end of each month.  Not just the last month in my selection of months. With a Dimension of Mth-Yr the results with the data above would look like this.

Mth-YrMonthEnd DateActive Customers
Dec-2016

12/31/2016

5
Jan-20171/31/20173

I have tried different variations of Aggr() expressions with no luck.

Thank you.

2 Replies

Re: End of month totals based on dates around each month end

For Dec 2016 active customers are 3... not 5.. please recheck and update. (i.e 002, 003, 006)

For Jan 2017 active customers are 3 which is correct.

You can use below script..

Data:

Load

  AccountID,

  If(Len(Trim(StartDt))=0,MonthEnd(Today()),StartDt) as StartDt,

  If(Len(Trim(DisconnectDt))=0,MonthEnd(Today()),DisconnectDt) as DisconnectDt

;

Load

  AccountID,

  Date(Date#(StartDt,'M/D/YYYY')) as StartDt,

  Date(Date#(DisconnectDt,'M/D/YYYY')) as DisconnectDt

Inline

[

  AccountID, StartDt, DisconnectDt

  001, 6/2/2015, 12/5/2016

  002, 2/22/2016,

  003, 4/12/2016, 1/18/2017

  004, 1/10/2017, 1/21/2017

  005, 1/20/2017,

  006, 7/14/2016, 2/2/2017

];

NoConcatenate

Final:

Load *, Date(MonthEndDate,'MMM-YYYY') as MonthYear;

Load

  AccountID,

  MonthEnd(StartDt, (IterNo() - 1)) as MonthEndDate

Resident Data

While MonthEnd(StartDt, (IterNo() - 1)) < MonthEnd(DisconnectDt)

;

Now create a straight table

Dimension

MonthYear

MonthEndDate

Expression

COUNT(DISTINCT AccountID)

Update : File enclosed for your ref..

darrin_pilkingt
Contributor II

Re: End of month totals based on dates around each month end

Thank you Manish for taking a look at this.

I think I need to find a solution that calculates this in the chart vs. in load script possibly using Aggr().

Your version works but I run into a couple of issues.

1. The application uses a binary load and takes about 60 seconds to load.  At the time of this post I am at 21 minutes and counting while trying to process all of the date combinations.  We have in our history over 2 million customers and this must create month end dates for each month going back 10 years or 120 months.

2. Unfortunately the way we will need to slice the data, having a single table for the account and their dates would not work in our data model.

Community Browser