I dint undertsandyour data set What is purchase in your excel ?and u told us csutomer is it Email?
What is first secod and 3 purchase ,please explain your question more clearly.
That is correct Amount = Purchase and Customer = Email. Each customer (identified by email) has multiple transactions. For example, user 1 has a first transaction on 12/6/06, followed by a second transaction on 5/28/11, a third transaction on 9/30/09, and a fourth on 3/3/09.
I am trying to understand if the 2nd purchase (the one on 3/3/09) was greater than the first (the one on 12/6/06) - both for user 1 and on average for the entire data set. Does that help clarify what the questions are trying to get at? Thanks!
Create a Calculated field
if ATTR([Amount])>Lookup(ATTR([Amount]),1) then 'Greater than' else 'Not Greater' end to koow which one is greater.
And WHat avg do u need?
I should have clarified I was looking for actual numbers, not literally if it is greater than or less than. I'll rephrase the questions below to try and clear everything up:
- For each user, can we rank order what frequency transaction it is by Date? (For example for user1, transaction 12/6/06 would be 1, while transaction 3/3/09 would be 2, and transaction 9/30 would be 3)
- Can we then calculate the difference between the Amount between the transaction and the user's first transaction (For example for user 1, comparing the subsequent transactions amounts to the first Amount of $135.25)
- Lastly, can we calculate an average of the first transaction, second transaction, third transaction and fourth transaction for all users (in this data set the average of the first transactions is $131.47, second transaction average is $158.33)?
Hopefully that adds more clarity? Thanks again!
Please see attached workbook
and i havent Done Step 3 How do i Get 131.45 what is it the avg of?
The avg for first email would be ( 134.25+99.83+79.8+250)/4=141.25 is this what u want?
Book1.twbx 33.9 KB
Thanks for reaching out and sharing your challenge!
Find my approach depicted in below screenshot and attached workbook version 9.0
All requested indicators are available.
a. Rank order per Customer
b. Delta against first order
c. Delta against previous order
d. Average order size per customer (LOD-expression)
Does it give a clear answer to your questions stated? No then we should go for a dashboard like approach based on a total different functionality used
Sample Workbook lay out.twbx 29.2 KB
Thanks Norbert! This is very helpful and appreciate your assistance here! Your approach definitely addresses almost all of my challenge.
I am having a bit of trouble with one last piece - lining up the average of the 2nd transaction, 3rd transaction, etc. for all users. I am thinking it is because the ranker function is set up as an attribute? I was attempting to use Date as the columns and ranker in the rows solving for the average of the Amount (see attached workbook if helpful).
Do you know if there would be a way to work within your framework to answer the question "what is the average 1st Amount, 2nd Amount, 3rd Amount etc. across all users"?