7 Replies Latest reply on Jan 9, 2017 10:40 PM by Alec Chen

# Calculating time elapsed based on an account number or unique identifier

Beginner here - title probably doesn't make much sense, but basically I have a Date, Transaction #, Customer Account #, Issue Amount, and Redemption Amount. So my data will look something like this:

DateTransaction #Customer Account #
Issue AmountRedemption Amount
1/1/2017001ABCDEFG500
1/5/2017002ABCDEFG050
1/6/2017003ZYXWVUT1000
1/6/2017004ZYXWVUT050
1/7/2017005ZYXWVUT050

From this data I want to find the average time between "Issue" and first "Redemption" but I am not sure how to go about it. Might just need to talk it out. Any ideas would be much appreciated!

• ###### 1. Re: Calculating time elapsed based on an account number or unique identifier

Hi Aelc,

To better understand your logic, can you add another Column at the end, showing the desired result of your logic?

Thanks,

Dhruv Gupta

• ###### 2. Re: Calculating time elapsed based on an account number or unique identifier

Hi Alec,

As per my understanding, account ABCDEFG has made first redemption on 1/5/2017 (date format assumed MM/DD/YYYY)and first transaction made on 1/1/2017. So day difference will be 4. Correct me If i'm wrong

Workbook version 9.3 attached for your reference. Let me know If this help. Else share your expected output.

Mahfooj

• ###### 3. Re: Calculating time elapsed based on an account number or unique identifier

Thanks for the responses. Yes Mahfooj, that is the desired result - however, it seems that the Avg of Redemption Day Difference for ZYXWVUT should be 0 since the First Redemption Day is the same day (0)?

If we do not want to display the Customer Account number for privacy reasons, how can we achieve this? How will we show the total average for all customers?

Thanks again!

• ###### 4. Re: Calculating time elapsed based on an account number or unique identifier

Hi Alec,

Just change the agg to AVG()

For Total Avg you can try this

workbook attached for your reference. Let me know If you've any query.

Mahfooj

• ###### 5. Re: Calculating time elapsed based on an account number or unique identifier

Thanks Mahfooj, that worked! Could you explain a little bit about how the Day Difference calculation works? I'm not familiar with FIXED..

• ###### 6. Re: Calculating time elapsed based on an account number or unique identifier

Hi Alec,

To get the clear view how the LODs are working here. Just break down the calculated field in multiple fields and drag individually in the canvas and see the result.

First LOD: {FIXED [Customer Account]: MIN([Date])} //It will calculate the first transaction date of each account.

Second LOD: {FIXED [Customer Account]: MIN(IF [Redemption Amount]>0 THEN [Date] END)} //It'll calculate the first date of redemption

Drag the [Customer Account] and LODs in the canvas and see the output. For more detail about FIXED{} LOD follow the link

One more thing, its a good practice to close the thread by marking my answer as correct. You can do that from action of your post.

Mahfooj

• ###### 7. Re: Calculating time elapsed based on an account number or unique identifier

Thanks Mahfooj for the explanation, very helpful. One more question since this is within the same topic - the "Total Avg" calculation, does this include any "First Day Redemption Day Difference" with NULL? I know I didn't include this in the original scenario, but I am experiencing this with my real data set, in which a product has not been redeemed yet.

Edit: Nevermind, I realized that the null values are not included