I am trying to find new subscribers per day and subscribers lost per day. I have been using previous() do it. I think I have found a way to find new customers but I cannot crack the issue with lost subscribers. The data is basically as
2015-02-01, 1 Start of subscription
2015-02-28, 1 Still a subscriber.
2015-02-01, 2 Start of subscription
2015-02-10, 2 - Lost customer
So the min(date) is the start date of the subscription , so I would like to mark him as new, the last max(date) is the day he stops, but if the max(date) is last day of month he is still a customer.
Here is my code as is, any hints would be greatly appreciated. On top of this I was thinking of calculating subscriper churn rate.
LOAD maxString(DagsStads) as MaxDags,
MinString(DagsStads) as MinDags
LET vMaxDags = PEEK('MaxDags');
LET vMinDags = Peek('MinDags');
DROP TABLE MaxDags;
if(Previous(KennitalaRetthafa) = KennitalaRetthafa and Previous(DagsStads) <> $(vMaxDags), 'Áskrift','Nýr') as Previ
Order by KennitalaRetthafa, DagsStads;
DROP Table SubsData;
oh wow Icelandic:)
I think you are on the right track with your code. I remember I had issues before with ORDER BY and previous peek functions in the same load statement. Try to do ORDER BY first and then resident load ordered table and use previous function at that point - so you would do ORDER BY on SubsData table not for NewSubs.
Also why your max/min dates are global and not subscriber related?
Yes sorry for the Icelandic, I was getting a bad from my wife when I was replying
I will try the Order By trick you mentioned.
Yes regarding the max/min dates, I thought about having it on a per subscriber base, how would you do that?
there are many ways...you could use RANK function and GROUP BY for example to rank group of rows that would relate to one SubscriberID.
OR you can just get away with GROUP BY on a resident table and storing min/max date in the third table by subscriberID and join it back to your main table.
Even with your test QVD (thanks for uploading it), it is a bit tough to understand your data and what you are trying to do.
I created a new file, this time not Icelandic added a qvw file. So maybe this will help to explain what I am trying to achieve.
So this is more or less the result I am after, but I am kind of lost in how to get there. The is to look at the first date in a snapshot table and mark a customer as new, if found in previous which is not new then it is an established subscription. If previous subsId changes the previous subscriber quit that day.
Again all hints are appreciated.
Boris thanks again for all your help.
Sorry for this my mistake, I added the QVD file and a bit more data, this customer 2212862789 stops his subscription on the 2014-03-11 and starts again on the 2015-01-26.
I'm still a puzzled with this,