# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor

## Counting new and closed clients per month

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!

1 Solution

Accepted Solutions

## Re: Counting new and closed clients per month

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

talk is cheap, supply exceeds demand
7 Replies

## Re: Counting new and closed clients per month

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

talk is cheap, supply exceeds demand
Contributor

## Re: Counting new and closed clients per month

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

MVP

## Re: Counting new and closed clients per month

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

Contributor

## Re: Counting new and closed clients per month

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]:

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

[MasterMonthCalendar]:

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]:

RESIDENT MasterCalendar;

[MasterSnapshotDate]:

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!

MVP

## Re: Counting new and closed clients per month

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]:

RESIDENT MasterCalendar;

[MasterSnapshotDate]:

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

Contributor

## Re: Counting new and closed clients per month

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.

Contributor

## Re: Counting new and closed clients per month

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