6 Replies Latest reply on Oct 4, 2018 10:53 PM by James D

Find % of Sales From 30+ Days ago Refunded within 30 days from the Sale

Hello. Hopefully someone knows an elegant solution to this problem.

My data is as follows (millions of rows):

customer_iddatetransaction_idtransaction_type
A1/1/1812345sale
B2/20/1865899sale
A1/29/1812345refund
B3/29/1865899refund
C4/1/18356987sale
A1/20/1865987sale
C6/1/18356988sale
D9/1/183216549sale

I want a table calc that shows, for each customer, the % of their transactions that were transaction_type = sale and refunded (based on matching transaction_id) within 30 days of the sale date. So it would be like this:

customer_idsales 30 days old, or older (relative to today's actual date of Sept 26 2018)refunds occurring within 30 days of saletable_calc
A2150%
B100%
C200%
D000%

A has 2 sales from at least 30 days ago, and has 1 refund that occurred within 30 days of the sale.

B has 1 sale from at least 30 days ago, and has 0 refunds that occurred within 30 days of the sale, and has 1 refund that occurred more than 30 days after the sale, so is not counted by the table_calc.

C has 2 sales from at least 30 days ago. and 0 refunds.

D has 1 sale that occurred less than 30 days ago, so this is not counted by the table_calc.

Thanks

• 1. Re: Find % of Sales From 30+ Days ago Refunded within 30 days from the Sale

HI James,

I am not sure only from this small sample that this really works or not on large sample data set, but anyways.

Self Join with Join-calculation

Thanks,

Shin

• 2. Re: Find % of Sales From 30+ Days ago Refunded within 30 days from the Sale

Hi James,

You can also use LOD as alternative approach.

[Sale Date]

{FIXED [Customer Id], [Transaction Id]: MAX(IF [Transaction Type] = 'sale' THEN [Date] END)}

[Refund Date]

{FIXED [Customer Id], [Transaction Id]: MAX(IF [Transaction Type] = 'refund' THEN [Date] END)}

[Sales >= 30 days]

COUNTD(IF {FIXED [Customer Id], [Transaction Id]: MAX(DATEDIFF('day', [Sale Date], TODAY()))} >= 30 THEN [Transaction Id] END)

[Refunds <= 30 days]

COUNTD(IF {FIXED [Customer Id], [Transaction Id]: [Sales >= 30 days]} > 0 AND

{FIXED [Customer Id], [Transaction Id]: MAX(DATEDIFF('day', [Sale Date], [Refund Date]))} <= 30 THEN [Transaction Id] END)

[Table Calc]

IFNULL([Refunds <= 30 days]/[Sales >= 30 days], 0)

Hope this helps.

Ossai

• 3. Re: Find % of Sales From 30+ Days ago Refunded within 30 days from the Sale

Sorry for tiny pointing, but this may include Immediate Refunds (less than 30 days old sales with refund within 30 days from sales date)

Need to add [Sales >=30 days] criteria in condition.

Shin

• 4. Re: Find % of Sales From 30+ Days ago Refunded within 30 days from the Sale

Thanks Shin for pointing out. Somehow I missed the overriding Sales >= 30 days condition. I edited both the refund and table calc formulas to include the condition. Hopefully, that's what James is looking for.

• 5. Re: Find % of Sales From 30+ Days ago Refunded within 30 days from the Sale

Hi James D

Following up.

If you have further questions, please reply to this post.

If the problem is solved, please mark either of answer as Correct / Helped to close the thread, not from inbox but from original post.

Thanks,

Shin

• 6. Re: Find % of Sales From 30+ Days ago Refunded within 30 days from the Sale

Thanks, all. This answer worked for me!