8 Replies Latest reply: Apr 15, 2016 8:48 AM by Maxime Dumas RSS

    Counting new and closed clients per month

    Maxime Dumas



      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:



        • Re: Counting new and closed clients per month
          Gysbert Wassenaar

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

            • Re: Counting new and closed clients per month
              Maxime Dumas

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

                • Re: Counting new and closed clients per month
                  Stefan Wühl

                  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

                    • Re: Counting new and closed clients per month
                      Maxime Dumas

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




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




                          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



                      LOAD FullDate As Account.CreationDate, *

                      RESIDENT MasterCalendar;



                      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!

                        • Re: Counting new and closed clients per month
                          Stefan Wühl

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





                          LOAD FullDate As Account.CreationDate, *

                          RESIDENT MasterCalendar;



                          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

                            • Re: Counting new and closed clients per month
                              Maxime Dumas

                              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.