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