Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

maxim1500
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
maxim1500
Contributor

Re: Counting new and closed clients per month

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

MVP
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

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

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:

TimeNew ClientsClosed Clients
Jan 13225-719.00
Feb 13297-637.00
Mar 13181-527.00
Apr 13199-583.00
May 13425-569.00
Jun 13158-505.00
Jul 13139-494.00
Aug 13167-466.00
Sep 13140-512.00
Oct 13173-812.00
Nov 13167-726.00
Dec 13140-416.00

Expected data (from the database):

 

TimeNew ClientsClosed Clients
Jan 131040-1064.00
Feb 131209-892.00
Mar 13777-745.00
Apr 13876-835.00
May 131298-842.00
Jun 13710-774.00
Jul 13598-725.00
Aug 13659-649.00
Sep 13723-641.00
Oct 13880-1101.00
Nov 13735-952.00
Dec 13488-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
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]:

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

maxim1500
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!

Community Browser