Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a question about counting customers first orders per week.
In my app I only have one data source thas has a similar strucutre to this:
CustomerID | OrderID | Week |
---|---|---|
11223344 | 555 | 20 |
95959595 | 666 | 20 |
10101010 | 777 | 21 |
11223344 | 888 | 21 |
95959595 | 999 | 22 |
11223344 | 000 | 22 |
10101010 | 444 | 22 |
What I want to accomplish is to count the first order per week, preferarably without modifying the Load Script.
For example.
CustomerID 11223344 has its first order on Week 20, so it should only be counted on that row in the table, not on week 21 and 22 even though it has placed orders there.
CustomerID 10101010 has its first order on Week 21, so it should only be counted there even though it also placed an order on Week 22
How can I accomplish this so I can could each customers first order/week?
Thankful for help!
Is this what you are looking for?
If yes, using Straight or Pivot table do below:
Dim: CustomerID, Week, OrderID
Expr: = Rank(Aggr(Count(OrderID), CustomerID))
Then in presentaion tab check
Supress zero values
Hi,
another solution might be:
hope this helps
regards
Marco