Counting new and closed clients per month

[maxim1500]

Hi,

I have a Client dimension with a CreationDate and CloseDate. I also have a fact table with a SnapshotDate (last day of month), ClientID and multiple measures.

I would be interested in getting the number of new clients and closed clients during the month on the same bar chart. I need a common date (I thought SnapshotDate) and the count of clients having the same month in both creation and close dates fields, but I cannot figure out how to write that using set analysis.

I was expecting something like this:

count({<[MonthlyFact.SnapshotDate.Calendar.FirstDayOfMonthDate] = {"=[Client.CreationDate.Calendar.FirstDayOfMonthDate]"}>} distinct ClientID)

The dimension is:

MonthlyFact.SnapshotDate.Calendar.FirstDayOfMonthDate

Thanks!

[Gysbert_Wassenaar · Accepted solution]

I don't think you can use set analysis for this. Try this expression:

count(distinct if( MonthlyFact.SnapshotDate.Calendar.FirstDayOfMonthDate] = [Client.CreationDate.Calendar.FirstDayOfMonthDate], ClientID))

[maxim1500]

Actually, I thought this was the correct answer, but the numbers are wrong.

[swuehl]

Could you describe a bit more detailed how your data model looks like, how the field values look like and what results you see and expecting instead. (Best by posting a small sample QVW).

For example, it's not clear how you derive

MonthlyFact.SnapshotDate.Calendar.FirstDayOfMonthDate from "SnapshotDate (last day of month)" and how Client.CreationDate.Calendar.FirstDayOfMonthDate is created from your client creation date

[maxim1500]

The First Day Of Month comes from the master calendar script, as follow:

Let varMinDate = date#('2013-01-01', 'YYYY-MM-DD'); 

Let varMaxDate = date#('2013-12-31', 'YYYY-MM-DD'); 

Let varMinDateNumber = Num(date#('2013-01-01', 'YYYY-MM-DD'));

Let varMaxDateNumber = Num(date#('2013-12-31', 'YYYY-MM-DD'));

[MonthTempCalendar]: 

LOAD

                Floor(Num(MonthEnd(AddMonths(Date($(varMinDateNumber)), IterNo() -1)))) As TempDateNumber,

                Date(Floor(Num(MonthEnd(AddMonths(Date($(varMinDateNumber)), IterNo() -1))))) as TempDate

                AutoGenerate 1 While AddMonths(Date($(varMinDateNumber)), IterNo() -1) <= Date($(varMaxDateNumber));

[MasterMonthCalendar]: 

Load 

    TempDateNumber as [Date Number],

               TempDate AS [Full Date],

               WeekDay(TempDate) as [Day Of Week],

               date(TempDate,'WWWW') as [Day Of Week Long],

               If (Num(WeekDay(TempDate)) > 0 AND Num(WeekDay(TempDate)) < 6, True(), False()) as [Is Week Day],

               Num(WeekDay(TempDate)) as [Day Number In Week], 

               Day(TempDate) As [Day Number In Month], 

               DayNumberOfYear(TempDate) As [Day Number In Year],

               WeekStart(TempDate) As [First Week Day],

               MonthStart(TempDate) As [First Day Of Month],

               YearStart(TempDate) As [First Day Of Year],

               IF(daystart(TempDate) = daystart(WeekStart(TempDate)),True(),False()) As [Is First Week Day],

               IF(daystart(TempDate) = daystart(MonthStart(TempDate)),True(),False()) As [Is First Day Of Month],

               IF(daystart(TempDate) = daystart(YearStart(TempDate)),True(),False()) As [Is First Day Of Year],

               WeekStart(TempDate) As [Last Week Day],

               MonthEnd(TempDate) As [Last Day Of Month],

               YearEnd(TempDate) As [Last Day Of Year],

               IF(daystart(TempDate) = daystart(WeekEnd(TempDate)),True(),False()) As [Is Last Week Day],

               IF(daystart(TempDate) = daystart(MonthEnd(TempDate)),True(),False()) As [Is Last Day Of Month],

               IF(daystart(TempDate) = daystart(YearEnd(TempDate)),True(),False()) As [Is Last Day Of Year],              

               (week(TempDate) - week(monthstart(TempDate)) + 1) As [Week Number In Month], 

               week(TempDate) As [Week Number In Year],                 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week And Year],

               Month(TempDate) As [Month Name],

               date(TempDate, 'MMMM') as [Month Name Long],  

               Num(Month(TempDate)) As [Month Number In Year],

               date(TempDate, 'MMM YY') as [Month And Year],               

               'Q' & Ceil(Month(TempDate)/3) as [Quarter],

               'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as [Quarter And Year],

               Year(TempDate) As [Year],

               year(TempDate)*12+month(TempDate) As [Total Number Of Months]

Resident MonthTempCalendar 

Order By TempDate ASC;

Then both dates are derived from this master calendar

[AccountCreationDate]:

LOAD FullDate As Account.CreationDate, *

RESIDENT MasterCalendar;

[MasterSnapshotDate]:

LOAD FullDate As SnapshotDate, *

RESIDENT MasterCalendar;

What the formulas give me:

Time	New Clients	Closed Clients
Jan 13	225	-719.00
Feb 13	297	-637.00
Mar 13	181	-527.00
Apr 13	199	-583.00
May 13	425	-569.00
Jun 13	158	-505.00
Jul 13	139	-494.00
Aug 13	167	-466.00
Sep 13	140	-512.00
Oct 13	173	-812.00
Nov 13	167	-726.00
Dec 13	140	-416.00

Expected data (from the database):

Time	New Clients	Closed Clients
Jan 13	1040	-1064.00
Feb 13	1209	-892.00
Mar 13	777	-745.00
Apr 13	876	-835.00
May 13	1298	-842.00
Jun 13	710	-774.00
Jul 13	598	-725.00
Aug 13	659	-649.00
Sep 13	723	-641.00
Oct 13	880	-1101.00
Nov 13	735	-952.00
Dec 13	488	-500.00

The model is a traditional star schema with a monthly fact containing Client ID on each row. The creation / close date are in a satellite dimension (Client dimension).

Hope this helps!

[swuehl]

Sorry, still not 100% sure how your data values look like.

As far as I can see, [Full Date] is created as a month end date (via TempDate).

Hence,

[AccountCreationDate]:

LOAD FullDate As Account.CreationDate, *

RESIDENT MasterCalendar;

[MasterSnapshotDate]:

LOAD FullDate As SnapshotDate, *

RESIDENT MasterCalendar;

shall link Account.CreationDate resp. SnapshotDate to month end dates in your master calendar.

[Not really understood this: Are you creating two more calendar tables from your master calendar? Wouldn't above result in a huge synth key table?]

But in your expressions, you are using different fields:

count(distinct if( MonthlyFact.SnapshotDate.Calendar.FirstDayOfMonthDate] = [Client.CreationDate.Calendar.FirstDayOfMonthDate], ClientID))

I think it would be easier to help you if you could post a small sample QVW.

Preparing examples for Upload - Reduction and Data Scrambling

[maxim1500]

My bad. The model changed since the original post.

MonthlyFact.SnapshotDate.Calendar.FirstDayOfMonthDate is now [MonthlySnapshotDate.FirstDayOfMonthDate]. We used the derived fields initially, and reverted to the original calendar table due to some issues with set analysis.

The are no synth keys since we delete the original MasterCalendar table. We only keep the copies and the fields are prefixed with the table name.

I tried to build a sample app, but I was not able to reproduce the issue in the smaller dataset. I guess the problem is probably somewhere in my app, not the script proposed by Gysbert Wassenaar.

[maxim1500]

Found the issue... The error was in the sql query actually... Qlik was right! Sorry about that! Thanks for your help!