Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So, I want to create a table that shows how the customer develops from one segment from one month to another.
Something like
if (iscustomeratleast6months=1,sum(salesamount)>1000) then segment=A
else if (iscustomeratleast6months=0) then Segment=B
else if (isCustomeratleast6months=1,atcollection=1) then Segment=C
and so on..
I should be able to do all these calculations from my tables Transactions and Customers.
So how do I create these segments, and show how the customers develop per month?
I've been googeling quite a lot, but my lack of technical expertise makes it hard to understand how to transfer the examples to my own dataset. Can any of you please help?
CustomerID | 2017_01 | 2017_02 | 2017_03 |
---|---|---|---|
12345 | A | A | B |
12346 | C | C | C |
12347 | C | C | C |
12348 | A | A | A |
Message was edited by: Xili Lin
Would you be able to share some raw sample data which includes the flags such as iscustomeratleast6months and other fields such as salesamount and CustomerID.
Tried to create a sample set. The customertable only shows todays values, so man plan is to make historic data from now on, but for now I've only got the present data. I do however have a lot of salesdata. I'm worried that the sales data might break the report if I use too many calculations. So what would be the smart thing to do?
How do you create iscustomeratleast6months flag? look at the max salesdate and compare it to Today - 6 months? Also, Sum(SalesAmount) is at the customer level? You need this on the front end of the application?
I don't create the iscutomeratleast6monthsflag, it is a set variable from the DWH. The salesamount is at customer level.
My main problem is where to create all the ifs, and how.
I am not sure I follow... your if statement uses this
if (iscustomeratleast6months=1,sum(salesamount)>1000) then segment=A
else if (iscustomeratleast6months=0) then Segment=B
else if (isCustomeratleast6months=1,atcollection=1) then Segment=C
and so on..
What is iscustomeratleast6months? How do I know if the value is 1 or 0?