4 Replies Latest reply: Oct 7, 2013 8:55 AM by Friedrich Hofmann RSS

    Creating weekly snapshot data?

      Hello,


      I have one table that stores all records of when customers started with the company and when they left (or if they are still an active customer they will have a leave date of 30/12/1899). What I want to do is create a weekly table that shows the number of active customers (by Area) and then compare to the previous week (and previous week last year) to view trends of what customers leave and join. The dataset is very much based on the below table so I'm struggling to create a structure that will handle this request. Using the below as an example I would want w/c 08/07/2013 to show that I had 4 active customers (Customer A, Customer B, Customer C and Customer D)

       

      Any help would be much appreciated.

       

      Thanks,


      Ralph

       

       

      AreaCustomerStartDateLeaveDate
      MarketingCustomerA05/06/201330/12/1899
      MarketingCustomerB01/07/201301/08/2013
      SalesCustomerC05/07/201317/07/2013
      SalesCustomerD08/07/201330/12/1899
      ProductionCustomerE24/07/201305/08/2013
      ProductionCustomerF02/08/201305/08/2013
      ProductionCustomerG25/08/201301/09/2013
      LogisticsCustomerH04/09/201330/12/1899
      LogisticsCustomerI06/09/201301/10/2013
        • Re: Creating weekly snapshot data?
          Friedrich Hofmann

          Hi Ralph,

           

          that is pretty easy. You could find plenty of articles on how to "create missing data". However, I have done the same with personell data - from a start_date and an end_date, I created a table with one record per day.

          Using this code (approx.), it should work:

           

           

          LOAD
              Customer,

              Area,

              StartDate,
              LeaveDate,

              IF(LeaveDate < TODAY(), LeaveDate, TODAY()) as EndDate,
              DATE(Eintritt + IterNo()-1) as Datum_MA,
             
          DATE(Eintritt + IterNo()-1) as %Datum,
          RESIDENT MA_Grunddaten_#2
          While IterNO() <= (EndDate - StartDate +1)
          ;


          Well, using this code you will get a lot of data, but then you'll have every possibility of displaying your data as you see fit.

           

          HTH

          Best regards,

           

          DataNibbler

            • Re: Creating weekly snapshot data?

              Thanks for that. Would the while statement strip out the current active customers as they would have a default leave date of 30/12/1899?

              Thanks,


              Ralph

                • Re: Creating weekly snapshot data?
                  Friedrich Hofmann

                  Hi Ralph,

                   

                  are you serious in saying that the currently active customers have a leaveDate in the far past?

                  Well, I guess you are since you have said it twice now.

                  No, of course not - if this is a standard date for all customers who are still active, you can just edit that IF-clause in the LOAD statement like

                   

                  >>> IF(LeaveDate = '30/12/1899', TODAY(), LeaveDate) as EndDate <<<

                   

                  That way, the table will be populated up to the present day for all customers who have this standardized LeaveDate (and are thus still active) and up to the LeaveDate for all others (who have already left).

                   

                  HTH

                  Best regards,

                   

                  DataNibbler

              • Re: Creating weekly snapshot data?
                Michele Barini

                Hi,

                you could use the week function to return the week number, applied both to the StartDate and LeaveDate(replacing today if the date is 30/12/1899).