Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to flag the new customers and existing customers. If customers places an order for the first time he is new customer. If customer already placed order he is new customer. Need to display report by monthly to show new and existing customers in that month. How can I compare every record loaded with previously loaded records and flag the new record? Appreciate your help.
To get you started...
Add a field and a sort order to your fact table load script:
LOAD
*,
If(CustomerID=Previous(CustomerID),0,1). AS FirstOrderFlag
FROM DataSource
ORDER BY CustomerID, OrderDate;
Now, assuming you have a Customer_Counter field in your customer table (1 AS Customer_Counter on every record) you can create a chart that has OrderMonth as the dimension and the expression:
Sum({<FirstOrderFlag={1} Customer_Counter)
will give you the number of new customers per month.
Sum({<FirstOrderFlag={0} Customer_Counter)
will give you the number of returning customers per month.
I think (it's late!)
Hope this helps,
Jason
Message was edited by: Jason Michaelides I missed a parenthesis in the IF statement - now corrected. Thanks to paulyeo for pointing it out.
I would modify my SQL import query to be something like this:
Select CustomerID,
RecordDate,
etc....,
case when CustomerID in (select distinct CustomerID from YourTable where max(RecordDate) < FirstDayCurrentMonth) then 'N' else 'Y' end as IsNewCustomer,
etc...
from YourTable
I'm too lazy to write out the exact SQL syntax but I trust you will get the general idea. If you need the exact code just let me know.