5 Replies Latest reply: Aug 16, 2017 10:04 AM by Felip Drechsler RSS

    Comparison between monthly loads

    Liv ma

      Suppose I have the following MONTHLY data structure:

      Month, Client_id, Monthly_Sales

       

      I load this data every month, from different files - one file for each month

       

      in Qlik I put all this information in a single table, where the field Month becomes just another field in the table and includes all the actual months I have data for

       

      my question is this:

      how can I write a script which identifies, each month, which clients are new compared to the previous month?

      I need to create a dimension where I flag these new clients, each month.

       

      so if now we are in August, i need a script which flags all clients which were not present in July in the August data, and so on

       

      after 1 year, I need to be able to answer the question: "how much have we sold to clients which were new clients in that year?"

       

      I cannot do that simply by comparing the month of December with January, because I might have clients which were new, but we only had them as clients for 1 month (say in August) and they are no longer in the December table - although they are still new clients from a yearly perspective

       

      thank you

      LM

        • Re: Comparison between monthly loads
          Felip Drechsler

          Hi Liv,

           

          An example for january and february, that would flag Client_id 11,12 and 13 as new customers.

           

          fakedataJan:

          Load * Inline

          [

          Client_id,Month,Monthly_Sales

          1,2017-01-01,100

          2,2017-01-01,900

          3,2017-01-01,500

          4,2017-01-01,1300

          5,2017-01-01,1400

          6,2017-01-01,100

          7,2017-01-01,99

          8,2017-01-01,50

          9,2017-01-01,10

          10,2017-01-01,104

          ];

           

          NoConcatenate

          fakedataFeb:

          Load * Inline

          [

          Client_id,Month,Monthly_Sales

          1,2017-02-01,100

          2,2017-02-01,900

          3,2017-02-01,500

          4,2017-02-01,1300

          5,2017-02-01,1400

          6,2017-02-01,100

          9,2017-02-01,99

          11,2017-02-01,50

          12,2017-02-01,10

          13,2017-02-01,104

          ];

           

          data:

          Load

          Client_id as Customer,

          date#(Month,'YYYY-MM-DD') as Date,

          Monthly_Sales,

          1 as [Flag Already in base]

          Resident fakedataJan;

           

          Concatenate(data)

          Load

          Client_id as Customer,

          date#(Month,'YYYY-MM-DD') as Date,

          Monthly_Sales,

          0 as [Flag Already in base]

          Resident fakedataFeb

          where not exists(Customer,Client_id);

           

          drop tables fakedataJan,fakedataFeb;

           

          Felipe

            • Re: Comparison between monthly loads
              Liv ma

              thank you for your reply

               

              unfortunately, your script loses some information that was in the initial tables, i.e. the sales from February for clients existing in January

               

              probably has to do with this part where not exists(Customer,Client_id)


              for my application it is important to preserve the initial databases as they were but add this extra flag - so even if we calculated cummulated monthly sales, so that the total sales figure is correct, I still need to know how much we sold to each customer each month


              (obviously my data structure does not have anything to do with the example in terms of fields, just the problem is the same in nature)

            • Re: Comparison between monthly loads
              Felip Drechsler

              Can't seem to edit my own post here,

              so I'm just adding another one with a sample QVW.

               

              Below the code, wich gives the client_id 11,12 and 13 as new customers.

               

              fakedataJan:

              Load * Inline

              [

              Client_id,Month,Monthly_Sales

              1,2017-01-01,100

              2,2017-01-01,900

              3,2017-01-01,500

              4,2017-01-01,1300

              5,2017-01-01,1400

              6,2017-01-01,100

              7,2017-01-01,99

              8,2017-01-01,50

              9,2017-01-01,10

              10,2017-01-01,104

              ];

               

              NoConcatenate

              fakedataFeb:

              Load * Inline

              [

              Client_id,Month,Monthly_Sales

              1,2017-02-01,100

              2,2017-02-01,900

              3,2017-02-01,500

              4,2017-02-01,1300

              5,2017-02-01,1400

              6,2017-02-01,100

              9,2017-02-01,99

              11,2017-02-01,1

              12,2017-02-01,2

              13,2017-02-01,3

              ];

               

              customers:

              Load

              distinct Client_id as Customer,

              1 as [Flag Already in base]

              Resident fakedataJan;

               

              Concatenate(customers)

              Load

              distinct Client_id as Customer,

              0 as [Flag Already in base]

              Resident fakedataFeb

              where not exists(Customer,Client_id);

               

              NoConcatenate

              data:

              Load

              *

              Resident fakedataJan;

               

              Concatenate(data)

              Load

              *

              Resident fakedataFeb;

               

              drop tables fakedataJan,fakedataFeb;

               

              left join(data)

              Load

              Customer as Client_id,

              [Flag Already in base]

              Resident customers;

               

              drop table customers;

               

              Felipe.